Mysql IF NOT EXISTS then syntax

Question

I can't manage to produce a working mysql query right now for what I want. I got as close as

IF NOT EXISTS(SELECT * 
              FROM Users 
              WHERE Downloads='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4') 
UPDATE Users 
SET `Downloads`='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4' 
WHERE `User`='andrewfree';

This fails, I was reading about INSERT IGNORE INTO but that hasn't helped either. What I am trying to do is add this hash to a users Download field if it doesn't exist in the table in anyones Download field.

SQL - Structured **Query** Language. It's not a programming language! — Lightness Races in Orbit, Sep 13 '11 at 09:01
@Tomalak: According to wikipedia, it **IS** a programming language: http://en.wikipedia.org/wiki/SQL — ypercubeᵀᴹ, Sep 13 '11 at 09:30
It's not a procedural language, agreed. But it does have a (standard) procedural extension (besides the many proprietary ones): http://en.wikipedia.org/wiki/SQL/PSM — ypercubeᵀᴹ, Sep 13 '11 at 09:36
I suppose in a sense it could be interpreted as a programming language. Alright then let me say this: it is not a declarative language! — Lightness Races in Orbit, Sep 13 '11 at 09:37
@Andrew it seems that at least one of the answers below must be working, if so, please mark one as correct (the hook left of the question) so this question is marked as resolved. — Angelo Fuchs, Dec 08 '11 at 11:58

Arnaud Le Blanc · Answer 1 · 2011-09-13T10:22:23.643

3

Move the EXISTS in the WHERE clause:

UPDATE Users
SET    `Downloads`='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4'
WHERE  `User`='andrewfree'
AND    NOT EXISTS(
           SELECT 1
           FROM   Users
           WHERE  Downloads='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4'
       )

Or add a unique key on Users.Downloads and use UPDATE IGNORE:

ALTER TABLE Users ADD UNIQUE KEY downloads_unique (Downloads);

UPDATE IGNORE Users SET Downloads = 'c63...' WHERE User='andrewfree';

If a row already exists with this Downloads value, the UPDATE will not be done.

With the IGNORE keyword, [...] Rows for which duplicate-key conflicts occur are not updated.

See UPDATE syntax.

edited Sep 13 '11 at 10:22

answered Sep 13 '11 at 08:46

Arnaud Le Blanc

98,321
23
206
194

mysql> UPDATE Users SET Downloads='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4' WHERE `User`='andrewfree' AND NOT EXISTS(SELECT 1 FROM Users WHERE Downloads='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4'); ERROR 1093 (HY000): You can't specify target table 'Users' for update in FROM clause – Andrew Sep 13 '11 at 09:37

StevieG · Answer 2 · 2011-09-13T10:10:21.050

UPDATE Users 
SET `Downloads`='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4' 
WHERE `User`='andrewfree'
AND NOT EXISTS(SELECT * 
              FROM Users 
              WHERE Downloads='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4')

EDIT:

Actually, the above statement will throw an error. I think this will do it (untested)

UPDATE Users u1 INNER JOIN Users u2 
                ON u2.Downloads='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4'
SET u1.`Downloads`='c6310e0ae33f9377f6ae7994bbafe6e20113aaf4' 
WHERE u1.`User`='andrewfree'

Mysql IF NOT EXISTS then syntax

2 Answers2