Wednesday, January 21, 2015

Inserting or updating in MySQL without a primary key or unique index OR how to use EXISTS in MySQL

I was trying to write a simple query in MySQL that only inserted a row if there wasn't one already there, else it would update the existing one. MySQL has a system to take care of that for you if you have a primary key or a unique index on the table you want to insert or update. You can choose between INSERT ... ON DUPLICATE KEY or REPLACE. However, what if you don't want to add an index on your table just for that?

T-SQL (Microsoft SQL Server) has a syntax that works like this:
IF EXISTS(SELECT * FROM MyTable WHERE y=2)
  UPDATE MyTable SET x=x+1 WHERE y=2;
ELSE
  INSERT INTO MyTable (x,y) VALUES(1,2);
END

MySQL also has an IF...THEN syntax as well as an EXISTS statement. The problem is that they work in a completely different way. IF has an extra THEN keyword, uses ELSEIF instead of ELSE and needs to end with an END IF. EXISTS works only in WHERE clauses. So, let's translate the query above in MySQL and hope for the best:
INSERT INTO MyTable (x,y) 
  SELECT 1,2 FROM DUAL
  WHERE EXISTS(SELECT * FROM MyTable WHERE y=2);
UPDATE MyTable SET x=x+1 WHERE y=2;

Notice several things: I am not using any IF and I am updating rows all the time after the conditional insert. I am selecting values from DUAL because any select with a where clause needs a table in MySQL and DUAL is a dummy one built into the engine. (SELECT 1 WHERE 2=2; is not valid in MySQL).

There is another way, closer to the original T-SQL query, but it doesn't use EXISTS. It looks like this:
IF (SELECT 1=1 FROM MyTable WHERE y=2) THEN
  UPDATE MyTable SET x=x+1 WHERE y=2;
ELSE
  INSERT INTO MyTable (x,y) VALUES(1,2);
END IF;

Thing to notice here: I am using 1=1 to return a TRUE value that will make the IF work. Also, PAY ATTENTION!, this doesn't work as a simple query. I spent the better half of an hour trying to understand where the syntax error was while trying to execute this directly. Any flow operations like IF or WHILE, etc, are only valid in "programs", the MySQL term for stored procedures and functions.

I hope I clarified things for you on this.

0 comments: