Yi Zhao Yi Zhao - 5 months ago 15
MySQL Question

Combine multiple update statements

I'm wondering would that be possible to combine the following two queries or more?
The current behavior is going to do the two times of table scan to the tableB, I wish after the combination then would be one time scan to the tableB.
Thanks.

update tableA A
set A.column2=B.column3
from tableB B
where A.column1 = B.column1
B.column2=1111
and B.column3 is not null;


update tableA A
set A.column2=B.column4
from tableB B
where A.column1 = B.column1
and B.column2=2222
and B.column4 is not null;

Answer

This would be sql-server syntax would be and I am pretty sure this crosses over but I have seen some articles that suggest other syntax for mysql. But basically use a join or where clause that will relate the tables and then use a case statement to choose your value. In the solution below you will never use the ELSE of the case statement but for good measure reference the current column value for anything that would match your join but not your case statement conditions.

Using a JOIN

UPDATE a
    SET Column1 = CASE
       WHEN b.Column1 = 1111 THEN b.Column2
       WHEN b.Column1 = 2222 THEN b.Column3
       ELSE a.Column1
    END
FROM
    [DatabaseA].TableA a
    INNER JOIN [DatabaseB].TableB b
    ON a.Column4 = b.Column4
    AND (
          (b.Column1 = 1111 AND b.Column2 IS NOT NULL)
          OR
          (b.Column1 = 2222 AND b.Column3 IS NOT NULL)
       )

Per your comment of not using a join.

UPDATE a
    SET Column1 = CASE
       WHEN b.Column1 = 1111 THEN (SELECT b.Column2 FROM TableB WHERE b.Column4 = a.Column4)
       WHEN b.Column1 = 2222 THEN (SELECT b.Column3 FROM TableB WHERE b.Column4 = a.Column4)
       ELSE a.Column1
    END
FROM
    TableA
WHERE
    EXISTS (
       SELECT *
       FROM
          TableB
       WHERE
          b.Colmn4 = a.Column4
          AND (
                (b.Column1 = 1111 AND b.Column2 IS NOT NULL)
                OR
                (b.Column1 = 2222 AND b.Column3 IS NOT NULL)
             )

That looks crazy but it will only update the rows you are intending to. Otherwise if you don't care if you update the entire column you could do something like this.

UPDATE a
    SET Column1 = CASE
       WHEN b.Column1 = 1111 THEN ISNULL((SELECT b.Column2 FROM TableB WHERE b.Column4 = a.Column4 AND b.Column1 = 1111),a.Column1)
       WHEN b.Column1 = 2222 THEN ISNULL((SELECT b.Column3 FROM TableB WHERE b.Column4 = a.Column4 AND b.Column1 = 2222),a.Column1)
       ELSE a.Column1
    END
FROM
    TableA

You can also do it with a single subselect if you are willing to update the entire table.

UPDATE a
    SET Column1 = ISNULL(

       (       SELECT (CASE WHEN b.Column1 = 1111 THEN b.Column2 ELSE b.Column3 END)
       FROM
          TableB
       WHERE
          b.Colmn4 = a.Column4
          AND (
                (b.Column1 = 1111 AND b.Column2 IS NOT NULL)
                OR
                (b.Column1 = 2222 AND b.Column3 IS NOT NULL)
             )
        ),a.Column1)    
FROM
    TableA

The trick in all of these answers is you have to be able to relate the SELECT statement of TableB with TableA otherwise you will not get your intended result.