conditional on duplicate key update

Question

I'm trying to insert a new row, but if the key already exists, I want to update the row ONLY if a certain other value is in the table is different. Is this possible in a mysql query/statement?

My table consist of the following columns: hat, mittens, name, last_update

hat+mittens make up the unique index (say the values for "hat" and "mittens" are colors)

Let's assume this is already in the table:

1. hat=blue mittens=green name=george last_update=tuesday
2. hat=red mittens=green name=bill last_update=monday

On a new key, I want to insert as usual. On duplicate key, I want to do an update ONLY IF the name changes, otherwise ignore. The reason for this is that I want to preserve the last_update value (timestamp).

hat=yellow mittens=purple name=jimmy -- insert new row
hat=blue mittens=green name=george -- ignore 
hat=blue mittens=green name=betty -- update row

Is this possible without using separate statements to first look up the existing row, compare values and then issue an update if necessary? If so, what would be the syntax?

---

Thanks for your responses. I tried all of them. Indeed, using just a simple UPDATE statement like

update tbl set name='george' where hat='blue' and mittens='green'

results in no rows being updated. But, using either

 INSERT INTO tbl (hat,mittens,name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name='george';

or

INSERT INTO tbl (hat, mittens, name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name=CASE WHEN name <> VALUES(name) THEN VALUES(name) ELSE name END;

somehow results in the row being updated (and the timestamp changed).

FWIW, this is the table I'm using:

CREATE TABLE `tbl` (
`hat` varchar(11) default NULL,
`mittens` varchar(11) default NULL,
`name` varchar(11) default NULL,
`stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
UNIQUE KEY `clothes` (`hat`,`mittens`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

MySQL is version 4.1.22 (perhaps this matters?) Again, my appreciation for all of the replies.

Answer 1

You need to do nothing, this is default behavior.

If your query selects a row for UPDATE but updated value remains the same, as in:

UPDATE  table
SET     col = col

, the timestamp also remains the same.

This row doesn't even count as affected, the query will return 0 rows affected.

Answer 2

You need INSERT ... ON DUPLICATE KEY UPDATE syntax.

Your query would look like this:

    INSERT INTO tbl (hat,mittens,name) VALUES ('blue','green','george')
    ON DUPLICATE KEY UPDATE name='george';

If you had a record with blue/green/george for hat/mittens/name already, no UPDATE would actually be performed, and your timestamp would not be updated. If however you had a record with blue/green/betty, then 'betty' would be overwritten with 'george', and your timestamp would be updated.

Answer 3

If you are performing a multiple INSERT (either by SELECT or providing multiple rows to VALUES), you can do the following:

INSERT INTO tbl (hat,mittens,name) VALUES
    ('yellow','purple','jimmy'),
    ('blue','green','george'),
    ('blue','green','betty')
ON DUPLICATE KEY UPDATE name = VALUES(name);

This will:

• Insert rows that aren't duplicates w/ resp. to the UNIQUE indexes
• Update the name column for the others (when the new value is different)

Caveat: the comments in the documentation on the ON DUPLICATE syntax says that the mysql_affected_rows() result afterwards isn't reliable.

Answer 4

You can use normal sql constructs in the ON DUPLICATE KEY syntax. So in order to do conditional updates during an insert you can do the following:

INSERT INTO tbl (hat, mittens, name) 
VALUES ('yellow','purple','jimmy')
ON DUPLICATE KEY UPDATE name = CASE WHEN name <> VALUES(name) 
                                    THEN VALUES(name) ELSE name END;

This will change the value to what you provided to the insert statement when it's different from what's in the row and will set the value to be what it already is if it hasn't changed and will result in MySQL not doing anything to the row preserving the last_update timestamp as Quassnoi pointed out.

If you wanted to make 100% sure that you weren't relying on the behavior of MySQL where it doesn't update a row if you set a value to itself you can do the following to force the timestamp:

INSERT INTO tbl (hat, mittens, name) 
VALUES ('yellow','purple','jimmy')
ON DUPLICATE KEY UPDATE name = CASE WHEN name <> VALUES(name) 
                                    THEN VALUES(name) ELSE name END
                      , last_update = CASE WHEN name <> VALUES(name) 
                                      THEN now() ELSE last_update END;

This will only update the last_update to now() when the name has changed else it will tell MySQL to retain the value of last_update.

Also, in the ON DUPLICATE KEY section of the statement you can refer to the columns in the table by their name and you can get the values that you provided to the insert statement values section using the VALUES(column_name) function.

---

The following is a log that shows that the last statement provided works even on 4.1 where the others don't work due to a bug that was fixed in version 5.0.

C:\mysql\bin>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22-community

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE `tbl` (
    -> `hat` varchar(11) default NULL,
    -> `mittens` varchar(11) default NULL,
    -> `name` varchar(11) default NULL,
    -> `stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    -> UNIQUE KEY `clothes` (`hat`,`mittens`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO tbl (hat,mittens,name) VALUES ('blue','green','george');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tbl;
+------+---------+--------+---------------------+
| hat  | mittens | name   | stamp               |
+------+---------+--------+---------------------+
| blue | green   | george | 2009-06-27 12:15:16 |
+------+---------+--------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO tbl (hat,mittens,name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name='george';
Query OK, 2 rows affected (0.00 sec)

mysql> select * from tbl;
+------+---------+--------+---------------------+
| hat  | mittens | name   | stamp               |
+------+---------+--------+---------------------+
| blue | green   | george | 2009-06-27 12:15:30 |
+------+---------+--------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO tbl (hat, mittens, name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name=CASE WHEN name <> VALUES(name) THEN VALUES(name) ELSE name END;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from tbl;
+------+---------+--------+---------------------+
| hat  | mittens | name   | stamp               |
+------+---------+--------+---------------------+
| blue | green   | george | 2009-06-27 12:15:42 |
+------+---------+--------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO tbl (hat,mittens,name) VALUES ('blue','green','george') ON DUPLICATE KEY UPDATE name = CASE WHEN name <> VALUES(name) THEN VALUES(name) ELSE name END, stamp = CASE WHEN name <> VALUES(name) THEN now() ELSE stamp END;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from tbl;
+------+---------+--------+---------------------+
| hat  | mittens | name   | stamp               |
+------+---------+--------+---------------------+
| blue | green   | george | 2009-06-27 12:15:42 |
+------+---------+--------+---------------------+
1 row in set (0.00 sec)

mysql>

---

Let me know if you have any questions.

HTH,

-Dipin