2

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.

StevieG
  • 8,639
  • 23
  • 31
Andrew
  • 203
  • 2
  • 10

2 Answers2

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.

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
1
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'
StevieG
  • 8,639
  • 23
  • 31