Aktualizacja wielu tabel w MySQL za pomocą LEFT JOIN

Mam dwie tabele i chcę zaktualizować pola W T1 dla wszystkich wierszy w lewym połączeniu.

Dla prostego przykładu zaktualizuj wszystkie wiersze następującego zestawu wyników: 

SELECT T1.* FROM T1 LEFT JOIN T2 ON T1.id = T2.id WHERE T2.id IS NULL

Instrukcja MySQL stwierdza, że:

Instrukcja aktualizacji wielu tabel może używać dowolnego typu join dozwolonego w instrukcji SELECT, np. LEFT JOIN.

Ale nie mogę znaleźć właściwej składni do tego w udokumentowanej aktualizacji wielu tabel.

Co to jest prawidłowa składnia?

Author: Tchoupi, 2009-04-30
Source

5 answers

UPDATE  t1
LEFT JOIN
        t2
ON      t2.id = t1.id
SET     t1.col1 = newvalue
WHERE   t2.id IS NULL

Zauważ, że dla SELECT bardziej efektywne byłoby użycie NOT IN / NOT EXISTS składnia: 

SELECT  t1.*
FROM    t1
WHERE   t1.id NOT IN
        (
        SELECT  id
        FROM    t2
        )

Zobacz artykuł na moim blogu po szczegóły wydajności:

Niestety, MySQL nie pozwala na użycie tabeli docelowej w zapytaniu podrzędnym w instrukcji UPDATE, dlatego musisz trzymać się mniej wydajnej składni LEFT JOIN.

 275
Author: Quassnoi,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-04-30 13:46:13

To samo można zastosować do scenariusza, w którym dane zostały znormalizowane, ale teraz chcesz, aby tabela miała wartości Znalezione w trzeciej tabeli. Poniższa tabela pozwoli Ci zaktualizować tabelę o informacje z trzeciej tabeli, która jest lubiana przez drugą tabelę. 

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;

Byłoby to przydatne w przypadku, gdy masz użytkowników i grupy, i chcesz, aby użytkownik mógł dodać własną odmianę nazwy grupy, więc pierwotnie chcesz zaimportować istniejące nazwy grup do pole, w którym użytkownik będzie mógł je modyfikować.

 23
Author: theprivileges,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-03-07 19:31:00 
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  |

Zaktualizuję tekst pola w tabeli a za pomocą 

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`

I dochodzimy do tego wyniku: 

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   |                        |
--------+-------------------------+

Gdzie akceptowane jest tylko jedno pole z tabeli B, ale dojdę do tego wyniku: 

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   |                                            |
+--------+--------------------------------------------+
 4
Author: DNS,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-05-14 16:55:36 
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`)
)
 0
Author: guest,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-08-20 14:58:05 
                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)
 -1
Author: Shyam Sharma,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-08-06 12:01:46