發表者討論內容

冷日
(冷日) 發表時間：2016/2/21 15:26

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15773

[轉貼]mysql update column with value from another table

mysql update column with value from another table

I have two tables, both looking like

id  name  value
===================
1   Joe     22
2   Derk    30

I need to copy the value of value from tableA to tableB based on check name in each table.

Any tips for this UPDATE statement?

In addition to this answer if you need to change t1.value according to t2.value dynamically you can do for example:

UPDATE tableB
INNER JOIN tableA ON tableB.name = tableA.name
SET tableB.value = IF(tableA.value > 0, tableA.value, tableB.value)
WHERE tableA.name = 'Joe'

you need to join the two tables:

for instance you want to copy the value of name from tableA into tableB where they have the same ID

UPDATE tableB t1
        INNER JOIN tableA t2
             ON t1.id = t2.id
SET t1.name = t2.name
WHERE t2.name = 'Joe'

UPDATE 1

UPDATE tableB t1
        INNER JOIN tableA t2
             ON t1.id = t2.id
SET t1.name = t2.name

UPDATE 2

UPDATE tableB t1
        INNER JOIN tableA t2
             ON t1.name = t2.name
SET t1.value = t2.value

Second possibility is,

UPDATE TableB
SET TableB.value = (
    SELECT TableA.value
    FROM TableA
    WHERE TableA.name = TableB.name
);

原文出處：sql - mysql update column with value from another table - Stack Overflow

回覆

平面展開

前一個主題 | 下一個主題 | |

| |

討論串

[轉貼]如何更新資料表欄位透過其他資料表（UPDATE FROM TABLE） (冷日
(冷日), 2013/9/25 6:13)
[轉貼]SQL UPDATE FROM 的使用方法 (冷日
(冷日), 2014/1/23 7:00)
[轉貼]SQL 從另一個表格更新資料 (冷日
(冷日), 2014/1/23 7:02)
» [轉貼]mysql update column with value from another table (冷日
(冷日), 2016/2/21 15:26)
[轉貼]mysql update 不能直接使用 select 的結果 (冷日
(冷日), 2018/6/14 13:36)

發表新主題

DB研討會 : [轉貼]如何更新資料表欄位透過其他資料表（UPDATE FROM TABLE）

討論串