對這文章發表回應
發表限制: 非會員 可以發表
發表者： 冷日 發表時間： 2016/2/21 15:26:03
mysql update column with value from another table
I have two tables, both looking like
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:
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 1
UPDATE 2
Second possibility is,
原文出處：sql - mysql update column with value from another table - Stack Overflow
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