CODEWITHSUNDEEP
mysqlsql
Patrick Eaton
Patrick Eaton

Reputation: 716

MySQL replace into and get last insert ID

In MySQL, will a REPLACE INTO query work with the LAST_INSERT_ID function?

Essentially, I have to insert into one table, only where it doesn't exist, but then either way insert into the second table with a foreign key constraint of the first table.

So ie. 

REPLACE INTO TABLE1(NAME) VALUES('unique');
SET @table1_id = LAST_INSERT_ID();
INSERT INTO TABLE2(TABLE1_ID, VALUE) VALUES(@table1_id, 'Test Value');

Will that function as intended on both an insert and an update?

Upvotes: 6

Views: 2548

Answers (1)

user1439838
user1439838

Reputation: 131

Yes, REPLACE INTO query affects the result of LAST_INSERT_ID() function.

Documentation states:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

That means if INSERT affects LAST_INSERT_ID() then REPLACE should do it too.

I have tested it with MySQL 8.

Upvotes: 4

Related Questions