CODEWITHSUNDEEP
mysql
Cystack
Cystack

Reputation: 3551

SQL syntax to increment field when double key is duplicate

I am building an application quite similar to jsfiddle.net, handling saving and forking of some kind.

I have a SQL table 'url' of 3 columns : the code of a given saved state, the version (1, 2, 3 etc.) of it, and the id of the saved state as is indexed in another table. Say I have the code of a saved state, and want to fork it (create a new version of it). How can I tell MySQL to INSERT a row in 'url' with the same code as provided, but a version number equal to the last version number used for this code + 1?

In short, this is the opposite of a ON DUPLICATE KEY UPDATE. I want that, on duplicate key, the insert be slightly different than the duplicated key.

Upvotes: 0

Views: 90

Answers (1)

pilcrow
pilcrow

Reputation: 58534

Assuming a code of 'foo' and an id of 1:

INSERT INTO url (code, version, id)
     SELECT 'foo', COALESCE(MAX(version) + 1, 1), 1 -- is 'foo' already there?
       FROM url 
      WHERE code = 'foo';

Upvotes: 1

Related Questions