UPDATE multiple tables in MySQL using LEFT JOIN

Question

I have two tables  and want to update fields in T1 for all rows in a LEFT JOIN     For an easy example  update all rows of the following result-set     SELECT T1   FROM T1 LEFT JOIN T2 ON T1 id   T2 id WHERE T2 id IS NULL     The MySQL manual states that        Multiple-table UPDATE statements can use any type of join allowed in SELECT statements  such as LEFT JOIN      But I cannot find the proper syntax for doing that in the documented multiple-tables UPDATE     What is the proper syntax

User · Answer

UPDATE  Table A  a SET a  text            SELECT group concat b  B-num    from   b  date  SEPARATOR                 FROM  Table B  b WHERE  a  A-num  b  A-num

User · Answer

DECLARE  cols VARCHAR max   colsUpd VARCHAR max    query VARCHAR max   queryUpd VARCHAR max    subQuery VARCHAR max  DECLARE  TableNameTest NVARCHAR 150  SET  TableNameTest    TableName    Staging   SELECT   colsUpd   STUF    SELECT DISTINCT     T1      name    T2    name    FROM sys columns                  WHERE object id                                         SELECT top 1 object id                                        FROM sys objects                                      WHERE name       TableNameTest                                                          and name not in   Action   Record ID                   FOR XML PATH                    1  2                         Select  queryUpd   Update T1 SET    colsUpd   FROM    TableName   T1 INNER JOIN    TableNameTest   T2 ON T1 Record ID   T2 Record Id WHERE T2  Action      Modify    EXEC   queryUpd

User · Answer

UPDATE  t1 LEFT JOIN         t2 ON      t2 id   t1 id SET     t1 col1   newvalue WHERE   t2 id IS NULL   Note that for a SELECT it would be more efficient to use NOT IN   NOT EXISTS syntax   SELECT  t1   FROM    t1 WHERE   t1 id NOT IN                   SELECT  id         FROM    t2             See the article in my blog for performance details    Finding incomplete orders  performance of LEFT JOIN compared to NOT IN   Unfortunately  MySQL does not allow using the target table in a subquery in an UPDATE statement  that s why you ll need to stick to less efficient LEFT JOIN syntax

User · Answer

Table A   -------- -----------    A-num    text              1                      2                      3                      4                      5                  -------- -----------   Table B  ------ ------ --------------    B-num   date           A-num       22     01 08 2003        2       23     02 08 2003        2        24     03 08 2003        1       25     04 08 2003        4       26     05 03 2003        4      I will update field text in table A with   UPDATE  Table A   Table B  SET  Table A   text  concat ws     Table A   text   Table B   B-num    from                                               Table B   date       WHERE  Table A   A-num     Table B   A-num    and come to this result   Table A   -------- ------------------------    A-num    text                           1      24 from 03 08 2003           2      22 from 01 08 2003                  3                                   4      25 from 04 08 2003           5                              -------- -------------------------    where only one field from Table B is accepted  but I will come to this result   Table A   -------- --------------------------------------------    A-num    text                                               1      24 from 03 08 2003                               2      22 from 01 08 2003   23 from 02 08 2003                 3                                                       4      25 from 04 08 2003   26 from 05 03 2003          5                                                   -------- --------------------------------------------

User · Answer

The same can be applied to a scenario where the data has been normalized  but now you want a table to have values found in a third table   The following will allow you to update a table with information from a third table that is liked by a second table    UPDATE t1 LEFT JOIN  t2 ON   t2 some id   t1 some id LEFT JOIN  t3  ON  t2 t3 id   t3 id SET   t1 new column   t3 column    This would be useful in a case where you had users and groups  and you wanted a user to be able to add their own variation of the group name  so originally you would want to import the existing group names into the field where the user is going to be able to modify it

[mysql] UPDATE multiple tables in MySQL using LEFT JOIN

Examples related to mysql

Examples related to sql-update