2

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.

1 Answer 1

2

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.

1
  • 1
    Aha, left join + if isnull, nice idea! Nov 17, 2013 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.