This is probably a daft question. I want to know if there is a way to get
a primary key value back from an INSERT statement for later use, without
having to do a second query afterwards.

I have a table that references the keys of values in two other tables and
I want to add a row to it using the key values looked up from the other
tables in SELECT sub-clause. That's okay, but sometimes these other
tables wont have a matching entry in which case I want to create one. Is
it possible to do this in one go, i.e. by putting an INSERT...ON
DUPLICATE KEY UPDATE statement in the sub-clause and returning the
affected row?

I hope that's clear. The data that might need to be inserted matches the
data searched on so it just seems a shame to break it up into multiple
queries when just knowing the primary key of the affected row would let
me pile everything into one statement, so I thought there might be a way
to do this. Am quite new to MySQL.

Thanks,

-Taliesin.

On Jan 4, 7:59 am, Taliesin Nuin <T.N...@bath.ac.uk> wrote:
> This is probably a daft question. I want to know if there is a way to get
> a primary key value back from an INSERT statement for later use, without
> having to do a second query afterwards.
>
> I have a table that references the keys of values in two other tables and
> I want to add a row to it using the key values looked up from the other
> tables in SELECT sub-clause. That's okay, but sometimes these other
> tables wont have a matching entry in which case I want to create one. Is
> it possible to do this in one go, i.e. by putting an INSERT...ON
> DUPLICATE KEY UPDATE statement in the sub-clause and returning the
> affected row?
>
> I hope that's clear. The data that might need to be inserted matches the
> data searched on so it just seems a shame to break it up into multiple
> queries when just knowing the primary key of the affected row would let
> me pile everything into one statement, so I thought there might be a way
> to do this. Am quite new to MySQL.
>
> Thanks,
>
> -Taliesin.

Whoa, in the MySQL manual I found this page titled "How to Get the
Unique ID for the Last Inserted Row." Sounds like it might be
relevent to your problem.

<http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html>

> ... i.e. by putting an INSERT...ON
> DUPLICATE KEY UPDATE statement in the sub-clause and returning the
> affected row?
>
> I hope that's clear. The data that might need to be inserted matches the
> data searched on so it just seems a shame to break it up into multiple
> queries when just knowing the primary key of the affected row would let
> me pile everything into one statement, so I thought there might be a way
> to do this. Am quite new to MySQL.

I lately ran into this trick:

INSERT INTO TableName(UniqueField, OtherField)
VALUES('somethingThatMayExist', 'SomeData')
ON DUPLICATE KEY UPDATE OtherField=VALUES(OtherField),
IdField=LAST_INSERT_ID(IdField);

Explanation:
IdField is the AutoIncrement field.
UniqueField is the field that "triggers" the duplicate key clause. If
the value does not exist yet in the table, it is inserted.
OtherField is just a field that has to be updated if the uniqueness
would be violated.

If you pass the LAST_INSERT_ID() function a parameter, this will be set
as the last insert ID. So in case the above statement inserts, it is
just the new ID. In case it updates, it is set to the existing ID by the
duplicate key clause.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

ZeldorBlat wrote:
> On Jan 4, 7:59 am, Taliesin Nuin <T.N...@bath.ac.uk> wrote:
>> This is probably a daft question. I want to know if there is a way to get
>> a primary key value back from an INSERT statement for later use, without
>> having to do a second query afterwards.
>>
>> I have a table that references the keys of values in two other tables and
>> I want to add a row to it using the key values looked up from the other
>> tables in SELECT sub-clause. That's okay, but sometimes these other
>> tables wont have a matching entry in which case I want to create one. Is
>> it possible to do this in one go, i.e. by putting an INSERT...ON
>> DUPLICATE KEY UPDATE statement in the sub-clause and returning the
>> affected row?
>>
>> I hope that's clear. The data that might need to be inserted matches the
>> data searched on so it just seems a shame to break it up into multiple
>> queries when just knowing the primary key of the affected row would let
>> me pile everything into one statement, so I thought there might be a way
>> to do this. Am quite new to MySQL.
>>
>> Thanks,
>>
>> -Taliesin.
>
> Whoa, in the MySQL manual I found this page titled "How to Get the
> Unique ID for the Last Inserted Row." Sounds like it might be
> relevent to your problem.
>
> <http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html>

Yes. What I want might be a bit off the wall. It just offends me when I
see my code doing two identical (and complicated) SELECT statements,
because I first have to check if something is there before I know
whether or not to add it, and then if I've added it, to carry out the
SELECT again to get the key.

Thanks for the link. I had a look at it earlier, but I wasn't sure if I
was understanding it correctly. There are two complications with that
page as a means of doing what I want - one is that it would streamline
my code if I could get a LAST_UPDATE_ID in place of a LAST_INSERT_ID.
But I can't seem to find such a function. The second is that I'm
carrying this out in two sub-clauses so I would need to call the
LAST_INSERT_ID twice, though I can work around that.

I may or may not be explaing this very well. A simplified example of
what I want (I've cut out lots of columns), is this:

INSERT INTO master_table (table1_key, table2_key, relevant_data) VALUES
(SELECT pri_key FROM table1 WHERE table1_identifier = "varA" LIMIT 1),
(SELECT pri_key FROM table2 WHERE table2_identifier = "varB" LIMIT 1),
"lots of information"
ON DUPLICATE KEY UPDATE

However, where those two SELECT statements are to get the key values
from table1 and table2, I want to do something different. Because there
might not be a matching entry in table1 or table2, I would like to do an
INSERT...IGNORE or INSERT...UPDATE ON DUPLICATE KEY, so that I know the
data gets into the table, whilst returning the primary key from the
INSERT. In reality the WHERE clause on both of these is much longer than
tableX_identifier = "var", hence I'm keen to avoid any extra SELECTs.

Thanks for your ,

-Taliesin.

Willem Bogaerts wrote:
>> ... i.e. by putting an INSERT...ON
>> DUPLICATE KEY UPDATE statement in the sub-clause and returning the
>> affected row?
>>
>> I hope that's clear. The data that might need to be inserted matches the
>> data searched on so it just seems a shame to break it up into multiple
>> queries when just knowing the primary key of the affected row would let
>> me pile everything into one statement, so I thought there might be a way
>> to do this. Am quite new to MySQL.
>
> I lately ran into this trick:
>
> INSERT INTO TableName(UniqueField, OtherField)
> VALUES('somethingThatMayExist', 'SomeData')
> ON DUPLICATE KEY UPDATE OtherField=VALUES(OtherField),
> IdField=LAST_INSERT_ID(IdField);
>
> Explanation:
> IdField is the AutoIncrement field.
> UniqueField is the field that "triggers" the duplicate key clause. If
> the value does not exist yet in the table, it is inserted.
> OtherField is just a field that has to be updated if the uniqueness
> would be violated.
>
> If you pass the LAST_INSERT_ID() function a parameter, this will be set
> as the last insert ID. So in case the above statement inserts, it is
> just the new ID. In case it updates, it is set to the existing ID by the
> duplicate key clause.
>
> Best regards,

Brilliant. I didn't understand quite how ON DUPLICATE KEY UPDATE works,
apparently. That's a nice solution. I can use this to resolve my
problem, but just because I want everything , could this work in a
sub-clause where there were more than one such INSERT, or would this
muck up the LAST_INSERT_ID field (I guess it would). If I can make this
work in a sub-clause then I can do everything in one neat little SQL
statement. If not, then I'm going to end up with a stored procedure (I
don't know what the overhead is like on those).

Many thanks for this,

-Taliesin.

>> I lately ran into this trick:
>>
>> INSERT INTO TableName(UniqueField, OtherField)
>> VALUES('somethingThatMayExist', 'SomeData')
>> ON DUPLICATE KEY UPDATE OtherField=VALUES(OtherField),
>> IdField=LAST_INSERT_ID(IdField);
>>

> ..., but just because I want everything , could this work in a
> sub-clause where there were more than one such INSERT, or would this
> muck up the LAST_INSERT_ID field (I guess it would). If I can make this
> work in a sub-clause then I can do everything in one neat little SQL
> statement. If not, then I'm going to end up with a stored procedure (I
> don't know what the overhead is like on those).

I do not know what you mean by a sub-clause, but this also works if
there is more than one row to update or insert (so also for INSERT ..
SELECT statements). The OtherField=VALUES(OtherField) part means: update
the OtherField field with the value I otherwise wanted to insert. Is
that what you mean?

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

Willem Bogaerts wrote:
>>> I lately ran into this trick:
>>>
>>> INSERT INTO TableName(UniqueField, OtherField)
>>> VALUES('somethingThatMayExist', 'SomeData')
>>> ON DUPLICATE KEY UPDATE OtherField=VALUES(OtherField),
>>> IdField=LAST_INSERT_ID(IdField);
>>>
>
>> ..., but just because I want everything , could this work in a
>> sub-clause where there were more than one such INSERT, or would this
>> muck up the LAST_INSERT_ID field (I guess it would). If I can make this
>> work in a sub-clause then I can do everything in one neat little SQL
>> statement. If not, then I'm going to end up with a stored procedure (I
>> don't know what the overhead is like on those).
>
> I do not know what you mean by a sub-clause, but this also works if
> there is more than one row to update or insert (so also for INSERT ..
> SELECT statements). The OtherField=VALUES(OtherField) part means: update
> the OtherField field with the value I otherwise wanted to insert. Is
> that what you mean?
>
> Best regards,

I phrased it badly - MySQL is still new to me. I just meant a
complicated expression if that makes more sense. Anyway, you've answered
my question whether you understood me or not.

Thanks a lot for this. Been very ful,

-Taliesin.

On Fri, 04 Jan 2008 15:13:18 +0000, Taliesin Nuin
<T.Nuin@bath.ac.uk> wrote:

>ZeldorBlat wrote:
>> On Jan 4, 7:59 am, Taliesin Nuin <T.N...@bath.ac.uk> wrote:
>>> This is probably a daft question. I want to know if there is a way to get
>>> a primary key value back from an INSERT statement for later use, without
>>> having to do a second query afterwards.
>>>
>>> I have a table that references the keys of values in two other tables and
>>> I want to add a row to it using the key values looked up from the other
>>> tables in SELECT sub-clause. That's okay, but sometimes these other
>>> tables wont have a matching entry in which case I want to create one. Is
>>> it possible to do this in one go, i.e. by putting an INSERT...ON
>>> DUPLICATE KEY UPDATE statement in the sub-clause and returning the
>>> affected row?
>>>
>>> I hope that's clear. The data that might need to be inserted matches the
>>> data searched on so it just seems a shame to break it up into multiple
>>> queries when just knowing the primary key of the affected row would let
>>> me pile everything into one statement, so I thought there might be a way
>>> to do this. Am quite new to MySQL.
>>>
>>> Thanks,
>>>
>>> -Taliesin.
>>
>> Whoa, in the MySQL manual I found this page titled "How to Get the
>> Unique ID for the Last Inserted Row." Sounds like it might be
>> relevent to your problem.
>>
>> <http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html>
>
>Yes. What I want might be a bit off the wall. It just offends me when I
>see my code doing two identical (and complicated) SELECT statements,
>because I first have to check if something is there before I know
>whether or not to add it, and then if I've added it, to carry out the
>SELECT again to get the key.
>
>Thanks for the link. I had a look at it earlier, but I wasn't sure if I
>was understanding it correctly. There are two complications with that
>page as a means of doing what I want - one is that it would streamline
>my code if I could get a LAST_UPDATE_ID in place of a LAST_INSERT_ID.
>But I can't seem to find such a function. The second is that I'm
>carrying this out in two sub-clauses so I would need to call the
>LAST_INSERT_ID twice, though I can work around that.

I'm not sure LAST_INSERT_ID() does what you expect it to
do. It only returns the PRIMARY KEY if that happens to be
an INTEGER AUTO_INCREMENT column.

http://dev.mysql.com/doc/refman/5.0/...last-insert-id

>I may or may not be explaing this very well. A simplified example of
>what I want (I've cut out lots of columns), is this:
>
>INSERT INTO master_table (table1_key, table2_key, relevant_data) VALUES
>(SELECT pri_key FROM table1 WHERE table1_identifier = "varA" LIMIT 1),
>(SELECT pri_key FROM table2 WHERE table2_identifier = "varB" LIMIT 1),
>"lots of information"
>ON DUPLICATE KEY UPDATE
>
>However, where those two SELECT statements are to get the key values
>from table1 and table2, I want to do something different. Because there
>might not be a matching entry in table1 or table2,

I assume :
CREATE TABLE table1 (
pri_key INTEGER
NOT NULL
PRIMARY KEY
AUTO_INCREMENT,
table1_identifier sometype
INDEX
) ENGINE InnoDB;

CREATE TABLE table2 (
pri_key INTEGER
NOT NULL
PRIMARY KEY
AUTO_INCREMENT,
table2_identifier sometype
INDEX
) ENGINE InnoDB;

CREATE TABLE master_table (
table1_key sometype
NOT NULL
INDEX
REFERENCES table1 (pri_key),
table2_key sometype
NOT NULL
INDEX
REFERENCES table2 (pri_key),
relevant_data sometype,
PRIMARY KEY (table1_key, table2_key)
) ENGINE InnoDB;

In case one or both of the subselects returns NULL, so you
end up inserting into master_table with a NULL key, which
would violent the (implicit) referential integrity.
That's why I added the NOT NULL clause to the tableX_key
columns.

>I would like to do an
>INSERT...IGNORE or INSERT...UPDATE ON DUPLICATE KEY, so that I know the
>data gets into the table, whilst returning the primary key from the
>INSERT. In reality the WHERE clause on both of these is much longer than
>tableX_identifier = "var", hence I'm keen to avoid any extra SELECTs.

A select which only retrieves keys from recently accessed
data will be quite fast, especially if the search columnin
the WHERE clause of your subSELECTs is indexed (like I did
in my assumption).

You may be able to combine all actions that should be
performed coherently in a stored procedure. In that way
client-server communication is reduced as the logic is
performed by the server.

>Thanks for your ,
>
>-Taliesin.

No ready-to-run solutions here, but I hope this is of
anyway.
--
( Kees
)
c[_] Nostalgia isn't what it used to be. (#86)

Taliesin Nuin wrote:
> This is probably a daft question. I want to know if there is a way to get
> a primary key value back from an INSERT statement for later use, without
> having to do a second query afterwards.
>
> I have a table that references the keys of values in two other tables and
> I want to add a row to it using the key values looked up from the other
> tables in SELECT sub-clause. That's okay, but sometimes these other
> tables wont have a matching entry in which case I want to create one. Is
> it possible to do this in one go, i.e. by putting an INSERT...ON
> DUPLICATE KEY UPDATE statement in the sub-clause and returning the
> affected row?
>
> I hope that's clear. The data that might need to be inserted matches the
> data searched on so it just seems a shame to break it up into multiple
> queries when just knowing the primary key of the affected row would let
> me pile everything into one statement, so I thought there might be a way
> to do this. Am quite new to MySQL.
>
> Thanks,
>
> -Taliesin.

As a DBA and previoulsy a database engineering support engineer, I tend
to use the data to self-describe the primary key - something like phone
number (generally unique) instead of relying on a generated key for the
PK. When I must use this method (generated key) - I use a trigger to
insert that id into child tables during the insert command so that key
exists in the child tables.

Michael Austin wrote:

> As a DBA and previoulsy a database engineering support engineer, I tend
> to use the data to self-describe the primary key - something like phone
> number (generally unique) instead of relying on a generated key for the
> PK. When I must use this method (generated key) - I use a trigger to
> insert that id into child tables during the insert command so that key
> exists in the child tables.
>

Thanks (to you and to others). The data itself is too unwieldy to use
without an auto-generated key - I would have to combine data from five
different columns of mixed data types to create a unique index out of
it. I don't know if that is a lot of overhead with the way MySQL works
or not, but had a hunch it might be so went with the autogenerated
route. Also makes it more resource friendly when using as the foreign
key in the main table, if I understand what you're saying correctly.

Cheers,

-Taliesin.