CODEWITHSUNDEEP
mysql
el_pup_le
el_pup_le

Reputation: 12189

On Duplicate Key INSERT (rather than UPDATE)

Why wouldn't MySQL have this feature?

INSERT INTO abc (a) VALUES ('bla')
ON DUPLICATE KEY
 INSERT INTO ...

Do I need a stored procedure to do this?

Upvotes: 0

Views: 247

Answers (1)

paxdiablo
paxdiablo

Reputation: 882586

Insert into where exactly? The same table? You already know that's impossible because the primary key already exists.

If you're talking about inserting the row into a different table, that's another thing altogether, not to be done with a single insert statement. That's because the general idea of the insert statement is to get a specific row into a specific table. That can usually be done with "instead-of"-type triggers.

That idea also means that you probably shouldn't try to use a single insert statement to insert a different row (such as changing the primary key) in the case where the original already exists. In that case, the primary key is almost certainly an artificial one in which case an auto-increment key would probably be the way to go.

The idea behind the insert ... on duplicate key update is get that row in whether it exists already or not. It does get a specific row into a specific table.

Upvotes: 4

Related Questions