Hi Aram, This is certainly possible in PostgreSQL, using
DROP TABLE l; CREATE TABLE l(k SERIAL PRIMARY KEY, v INT UNIQUE); INSERT INTO l (v) VALUES (100) ON CONFLICT (v) DO UPDATE SET v = l.v RETURNING id; But if I remember correctly, you're using MySQL? I don't think MySQL can do this in a single query. Also, I don't think it's a good idea to do this in PostgreSQL, as it incurs too much locking when looking up the value. I'd just run a SELECT and conditionally, an INSERT if the SELECT doesn't return anything. Or vice versa, depending on what happens more often. Hope this helps, Lukas 2018-03-18 14:12 GMT+01:00 Aram Mirzadeh <[email protected]>: > Hi, > > I may be misremembering this but I thought I saw that there was a jOOQ or > lambda jOOQ update that you could use to get the id of a row from a lookup > table or add it as a new entry to the lookup table and return the new id? > > What I would like to do is cache the data from the lookup tables (they're > relatively small) then insert into the Table1 and for each field get the K > of the existing V or insert the V and get a new K. > > This is what I tried: > > LookupTableRecord r = context.insertInto(LOOKUPTABLE).columns( > LOOKUPTABLE.VALUE).values(myV).onDuplicateKeyIgnore(). > returning().fetchOne(); > > but the onDuplicateKeyIgnore is nulling out the returning() function as > nothing is coming back. > > Thanks. > > P.S. LookupTable.ID does have a PREINSERT trigger to create a new K ... > and there is a UNIQUE index on V. > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
