Mysql update if (some statement)

Question

I wanna create a mysql update only if the table does not contain the specified value(if player have empty slot)

My Idea:

update player_items 
set slot=20 
where id=0 
and uid=2 
and (SELECT COUNT(*) 
     FROM player_items 
     WHERE slot=12 and uid=2) = 0;

code in sqlfiddle

I hope this can be only one mysql question

spencer7593 · Accepted Answer · 2013-07-11 20:19:08Z

1

One way to do this is using the anti-join pattern, e.g.

UPDATE player_items p
  LEFT
  JOIN ( SELECT uid
           FROM player_items
          WHERE slot = 12
            AND uid = 2
          LIMIT 1
       ) q
    ON q.uid = p.uid
   SET p.slot = 20
 WHERE p.id = 0
   AND p.uid = 2
   AND q.uid IS NULL

The LEFT JOIN looks for a matching row, but if no matching row is found then MySQL will generate a row with all NULL values. We can filter out any rows that matched using the q.uid IS NULL predicate, so we will only get rows where there was no match.

mysql> UPDATE player_items p
    ->   LEFT
    ->   JOIN ( SELECT uid
    ->            FROM player_items
    ->           WHERE slot = 12
    ->             AND uid = 2
    ->           LIMIT 1
    ->        ) q
    ->     ON q.uid = p.uid
    ->    SET p.slot = 20
    ->  WHERE p.id = 0
    ->    AND p.uid = 2
    ->    AND q.uid IS NULL
    -> ;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

edited Jul 11, 2013 at 20:19

answered Jul 11, 2013 at 19:18

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2573981 Over a year ago

spencer7593 this not works :( I got an error in my mysql and SQL Fiddle (Schema Creation Failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE player_items)

spencer7593 Over a year ago

I had the SET clause is in the wrong position, it belongs before the WHERE clause. I've modified the query, and it seems to work for me.

Collectives™ on Stack Overflow

Mysql update if (some statement)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related