ON DUPLICATE KEY UPDATE with a WHERE and COUNT condition...?

Question

I would have liked to use a WHERE statement on a ON DUPLICATE KEY UPDATE statement in MySQL, however I understand you can't do this. I wanted my statement to be something like this..

INSERT INTO table1(name, user, num_users) 
VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE 
num_users = SELECT COUNT(users.table2) WHERE name=%s)
(name, user, 1, name))

So if anyone know how to do this in one command that would be great.

Thanks,

Could you describe the problem you are actually trying to solve. It's hard to tell what you expect the code to do, especially when the parenthesis don't match. — Dan Bechard, Apr 23 '13 at 18:49
I highly doubt your desired syntax possible, but you can use a trigger to do it. — Marc B, Apr 23 '13 at 18:54
please take a look here: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html and here: http://stackoverflow.com/questions/2714587/mysql-on-duplicate-key-update-for-multiple-rows-insert-in-single-query — jcho360, Apr 23 '13 at 19:34

score 0 · Answer 1 · answered Apr 24 '13 at 00:36

You can use a WHERE, and a subquery, in the ON DUPLICATE KEY UPDATE clause. Try using the correct syntax for that SELECT. I think this is wrong:

SELECT COUNT(users.table2) WHERE name=%s

Is table2 a table? In that case, there's something totally wrong. If it is a field, just try this:

SELECT COUNT(table2) FROM users WHERE name=%s

ON DUPLICATE KEY UPDATE with a WHERE and COUNT condition...?

1 Answers1