How to INSERT a record or UPDATE if it already exists?
MySQL supports the insert-on-duplicate syntax, f.e.:
INSERT INTO table (key,col1) VALUES (1,2) ON DUPLICATE KEY UPDATE col1 = 2;
If you have solid constraints on the table, then you can also use the REPLACE INTO
for that. Here's a cite from MySQL:
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.
The syntax is basically the same as INSERT INTO
, just replace INSERT
by REPLACE
.
INSERT INTO messages (sender, sent_time, status) VALUES (@sender, time, @status)
would then be
REPLACE INTO messages (sender, sent_time, status) VALUES (@sender, time, @status)
Note that this is a MySQL-specific command which doesn't occur in other DB's, so keep portability in mind.
As others have mentioned, you should use "insert...on duplicate key update", sometimes referred to as an "upsert". However, in your specific case you don't want to use a static value in the update, but rather the values you pass in to the values clause of the insert statement.
Specifically, I think you want to update two columns if the row already exists:
1) sent_time2) status
In order to do this, you would use an "upsert" statement like this (using your example):
INSERT INTO messages (sender, sent_time, status) VALUES (@sender, time, @status)ON DUPLICATE KEY UPDATE sent_time = values(sent_time), status = values(status);