Mysql insert on the first empty column on duplicate key update

Question

I'm trying to set a query to insert data on the first empty column or, if key value exists, update on the first empty column

"INSERT INTO table 
(myid,tr1)
VALUES ('12345', '$data') 
ON DUPLICATE KEY UPDATE
tr1 = CASE WHEN tr1 = '' ELSE tr1 END,
tr2 = CASE WHEN tr2 = '' ELSE tr2 END,
tr3 = CASE WHEN tr3 = '' ELSE tr3 END = $data"

where myid is unique key, in other words:

--------------------------
| myid | tr1 | tr2 | tr3 |
--------------------------
| 1233 | data|     |     |
--------------------------
|      |     |     |     |
--------------------------

When my id matchs, it updates $data in the next empty column, in this case for myid=1233 column would be tr2. When myid does not exist it insert myid and $data on tr1. The query is getting syntax error would appreciate any help!

Answer 1

Maybe the problem is in case clause. Try with this example.

I made some changes on the query, now should work. EXAMPLE

INSERT INTO `table` (myid,tr1)
VALUES ('12345', '111') 
ON DUPLICATE KEY UPDATE
tr3 = CASE WHEN tr2 != '' AND tr3 = '' THEN '111' ELSE tr3 END,
tr2 = CASE WHEN tr1 != '' AND tr2 = '' THEN '111' ELSE tr2 END,
tr1 = CASE WHEN tr1 = '' THEN '111' ELSE tr1 END;