Bug #6946 INSERT ... ON DUPLICATE KEY UPDATE without INSERT
Submitted: 2 Dec 2004 14:31 Modified: 14 Feb 2006 14:28
Reporter: Olaf van der Spek (Basic Quality Contributor) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:* OS:Any (*)
Assigned to: CPU Architecture:Any

[2 Dec 2004 14:31] Olaf van der Spek
Description:
Is there a way to use the ON DUPLICATE KEY UPDATE functionality without the INSERT part?
Such that when a key is not found, no insert or update is done?

I'd like to be able to do the following 3 updates in a single query (in a way that scales to 100s of such updates).

How to repeat:
create table t (a int, b int); 
update t set b = ? where a = 1; 
update t set b = ? where a = 2; 
update t set b = ? where a = 3;
[23 Dec 2004 11:55] Hartmut Holzgraefe
why not just "update t set b = ? where a IN (1,2,3);" ?
[23 Dec 2004 12:21] Olaf van der Spek
Because the ? is different in each query.
[15 Jan 2005 16:17] Olaf van der Spek
Why is the status still "Need Feedback"?
[13 Jun 2005 22:06] Ondra Zizka
I think that you described the current behavior of UPDATE, didn't you? When you UPDATE ... WHERE key="non-existent-value", nothing happens.

mysql> SELECT * FROM tabulka;
+----+------+------+
| id | val  | val2 |
+----+------+------+
|  1 | x    | E    |
+----+------+------+
1 row in set (0.00 sec)

mysql> UPDATE tabulka SET val="a" WHERE id = 0;
Query OK, 0 rows affected (0.02 sec)
Rows matched: 0  Changed: 0  Warnings: 0
[13 Jun 2005 22:08] Olaf van der Spek
No, I didn't. The create table is only there to show the structure of the table, not to indicate that the table is empty.
[13 Jun 2005 22:55] Ondra Zizka
Sorry but I don't get what you mean, and the status is still "Need Feedback" because no one else does. Perhaps you should provide simple but complete example with creating & filling the table, and also with updating with suggested SQL syntax...

Also have a look at http://bugs.mysql.com/bug.php?id=10396 whether that's the same idea.
[14 Jun 2005 7:41] Olaf van der Spek
> Sorry but I don't get what you mean, 

I simply want an equivalent single-query version of the three update statements (where ? can be different in each update).

> and the status is still "Need Feedback"
because no one else does.

Is it?
I see "Status: Open".

10396 isn't the same idea. I wish to avoid the insert that "insert ... on duplicate key update" does.
[4 Aug 2005 22:31] Jim Winstead
This can done with judicious use of an UPDATE statement and the CASE function.
[4 Aug 2005 22:42] Olaf van der Spek
You're kidding right?
That version is way more complex and a lot longer than necessary.
And it's much harder to generate too.
[4 Aug 2005 23:22] Jim Winstead
Sorry, but I'm not kidding. Since you haven't provided an example of a specific query in the syntax you propose, I fail to see how that hypothetical syntax is easier than what is already available.

Here's what I think you are after, using a regular UPDATE and CASE.

UPDATE t SET b = CASE a WHEN 1 THEN 'one' WHEN 2 THEN 'two' WHEN 3 THEN 'three' ELSE b END;

Please propose a counter-example.

Thanks.
[4 Aug 2005 23:34] Olaf van der Spek
Your example doesn't scale well to 1000 rows and 10 fields.
My example:
update into t (a, b) values (1, 'one'), (2, 'two'), (3, 'three');

This does require a to be a key, but that's acceptable (on duplicate key update does that too).

> Is there a way to use the ON DUPLICATE KEY UPDATE functionality without the
> INSERT part?
> Such that when a key is not found, no insert or update is done?
[5 Aug 2005 0:16] Jon Stephens
http://dev.mysql.com/doc/mysql/en/sqlps.html, anyone?
[21 Sep 2005 8:08] Olaf van der Spek
When and why did the status become won't fix?
[12 Feb 2006 12:42] Valeriy Kravchuk
Can you provide a name of RDBMS that supports this feature? I do not think that it is needed, really. In case of many rows updates, you can create a (temporary) table, put new values there and just perfrom one UPDATE, joining two tables...
[12 Feb 2006 12:45] Olaf van der Spek
> Can you provide a name of RDBMS that supports this feature? 

No, I can't. I've no experience with other RDBMSs. I also don't understand why another RDBMS has to implement this first.

> I do not think that it is needed, really. In case of many rows updates, you can create a (temporary)
table, put new values there and just perfrom one UPDATE, joining two tables...

No, I can't.
It performs worse, is much more complex and doesn't work if there are two updates for the same row in the update table.
[14 Feb 2006 14:16] Valeriy Kravchuk
I don't think that this non-standard feature needs to be implemented, anyway. Not is any foreseeable future.
[14 Feb 2006 14:28] Olaf van der Spek
Do you have any arguments for that?

What alternative do you suggest? Using 10000 single-row update queries?