Mysql update different fileds set depend on condition

conditionMySQLupdate

I have two tables "a" : 

CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `i1` int(11) DEFAULT NULL,
  `i2` int(11) DEFAULT NULL,
  `ib` int(11) DEFAULT NULL,
  KEY `id` (`id`)
)

 +----+------+------+------+
| id | i1   | i2   | ib   |
+----+------+------+------+
|  1 |    1 |    1 |   10 |
|  2 |    1 |    1 |    1 |
|  3 |    1 |    1 |    2 |
|  4 |    1 |    1 |    3 |
|  5 |    1 |    1 |    5 |
+----+------+------+------+

and "b"

CREATE TABLE `b` (
  `i` int(11) DEFAULT NULL
)

+------+
| i    |
+------+
|    5 |
|    1 |
|   10 |
+------+

My script parse external data (jid,j1,j2) and update table "a".
With 2 commands:

update a set i1=j1, i2=j2 where id=jid 
   and not exists (select 1 from b where a.ib=b.i)

and 

update a set i1=j1 where id=jid 
  and exists (select 1 from b where a.ib=b.i)

So, I want to update both fields i1 and i2 if I have not related record in "b" table, and update only one field i1 if I have related record.

If I have input data

(1,2,3)
(2,5,6)
(3,7,8)

My table "a" should be:

+----+------+------+------+
| id | i1   | i2   | ib   |
+----+------+------+------+
|  1 |    2 |    1 |   10 |   <- i1 changed 
|  2 |    5 |    1 |    1 |   <- i1 changed
|  3 |    7 |    8 |    2 |   <- both i1 and i2 changed
|  4 |    1 |    1 |    3 |
|  5 |    1 |    1 |    5 |
+----+------+------+------+

I am pretty sure – it can be done with only one sql command.

Best Answer

I like the JOIN syntax, it's clearer to me than subqueries:

UPDATE a LEFT JOIN b ON (a.ib=b.i) 
SET i1 = j1, 
    i2 = if(isnull(b.i), j2 ,i2) 
WHERE a.id = jid;

This will change the jid record: i1 to j1 always and i2 to j2 if no match exists, and to the same value if the match exists. You may want to index b.i (probably as a primary key) if there are a lot of records and you want performance.

Related Question