0

I have a table that has an auto incremented id PK, and, among others columns, a column that has a non-unique index on it, but there is data in it I know IS unique, named column4.

Is it possible to do an 

(INSERT if column4="this_data") or (UPDATE if column4!="this_data")

?

Or am I best just to use two queries - a SELECT to get the PK id of the row where column4="this_data", and then either an INSERT or UPDATE once I know whether the row exists?

Thanks for your time and help.

Shaun
  • 2,043
  • 3
  • 27
  • 36
  • 1
    http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html check this link – Abhishek Sharma Nov 02 '15 at 12:52
  • Why not just add the unique key? – Arth Nov 02 '15 at 13:00
  • The column holds user ids only if the user is of a particular type - for all other users I have zeros in there, which means I can't have a unique index. So, an example of data in that column from 5 rows might be 0,0,343555,0,0. Actually, should I replace the zeros with NULL? Can you have multiple nulls in a unique indexed column? – Shaun Nov 02 '15 at 13:03
  • Thanks guys. I set the default of column4 to NULL, so now it will accept a UNIQUE index, which allows the ON DUPLICATE KEY UPDATE to work. – Shaun Nov 02 '15 at 13:31

1 Answers1

0

Use INSERT ON DUPLICATE KEY UPDATE

INSERT INTO table (col) 
VALUES ('$val')
ON DUPLICATE KEY UPDATE col=col+1;

Or you can try :-

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

More details

Abhishek Sharma
  • 6,689
  • 1
  • 14
  • 20
  • Thanks Rahautos. That isn't working, as they can have multiple rows the way the indexes are set up, so the ON DUPLICATE KEY doesn't get triggered. – Shaun Nov 02 '15 at 12:59
  • This relies on the column having a unique key, which the OP explicitly stated that it doesn't – Arth Nov 02 '15 at 13:00
  • for more details please check this :) – Abhishek Sharma Nov 02 '15 at 13:05
  • http://stackoverflow.com/questions/14797510/fastest-way-to-update-a-mysql-table-if-row-exists-else-insert-more-than-2-non-u – Abhishek Sharma Nov 02 '15 at 13:06
  • Both of your references support my point.. from the dev docs: 'If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.' Now that the OP has added the UNIQUE key it will, so I will remove the down vote if you add this to the answer. – Arth Nov 03 '15 at 21:17