mysql 使用另一个表中的值更新列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11709043/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql update column with value from another table
提问by LeoSam
I have two tables, both looking like
我有两张桌子，看起来都像
id name value
===================
1 Joe 22
2 Derk 30
I need to copy the value of valuefrom tableAto tableBbased on check name in each table.
我需要的值复制value从tableA到tableB基于每个表检查名称。
Any tips for this UPDATEstatement?
这个UPDATE声明有什么提示吗？
回答by RafaSashi
In addition to this answer if you need to change tableB.value according to tableA.value dynamically you can do for example:
除了这个答案，如果您需要根据 tableA.value 动态更改 tableB.value，您可以执行以下操作，例如：
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'
回答by John Woo
you need to join the two tables:
你需要加入这两个表：
for instance you want to copy the value of namefrom tableA into tableBwhere they have the same ID
例如，您想将nametableA 中的值复制到tableB它们具有相同ID
UPDATE tableB t1
INNER JOIN tableA t2
ON t1.id = t2.id
SET t1.name = t2.name
WHERE t2.name = 'Joe'
UPDATE 1
更新 1
UPDATE tableB t1
INNER JOIN tableA t2
ON t1.id = t2.id
SET t1.name = t2.name
UPDATE 2
更新 2
UPDATE tableB t1
INNER JOIN tableA t2
ON t1.name = t2.name
SET t1.value = t2.value
回答by Samir Alajmovic
Second possibility is,
第二种可能是
UPDATE TableB
SET TableB.value = (
SELECT TableA.value
FROM TableA
WHERE TableA.name = TableB.name
);
回答by raul7
The second option is feasible also if you're using safe updates mode (and you're getting an error indicating that you've tried to update a table without a WHERE that uses a KEY column), by adding:
如果您使用安全更新模式（并且您收到一个错误，表明您尝试更新没有使用 KEY 列的 WHERE 的表），第二个选项也是可行的，添加：
UPDATE TableB
SET TableB.value = (
SELECT TableA.value
FROM TableA
WHERE TableA.name = TableB.name
)
**where TableB.id < X**
;
回答by aitbella
UPDATE cities c,
city_langs cl
SET c.fakename = cl.name
WHERE c.id = cl.city_id
回答by Abid Mahmood
Store your data in temp table
将您的数据存储在临时表中
Select * into tempTable from table1
Now update the column
现在更新列
UPDATE table1
SET table1.FileName = (select FileName from tempTable where tempTable.id = table1.ID);
回答by justadev
In my case, the accepted solution was just too slow. For a table with 180K rows the rate of updates was about 10 rows per second. This is with the indexes on the join elements.
就我而言，公认的解决方案太慢了。对于具有 180K 行的表，更新速率约为每秒 10 行。这是连接元素上的索引。
I finally resolved my issue using a procedure:
我终于使用一个程序解决了我的问题：
CREATE DEFINER=`my_procedure`@`%` PROCEDURE `rescue`()
BEGIN
declare str VARCHAR(255) default '';
DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE cur_name VARCHAR(45) DEFAULT '';
DECLARE cur_value VARCHAR(10000) DEFAULT '';
SELECT COUNT(*) FROM tableA INTO n;
SET i=0;
WHILE i<n DO
SELECT namea,valuea FROM tableA limit i,1 INTO cur_name,cur_value;
UPDATE tableB SET nameb=cur_name where valueb=cur_value;
SET i = i + 1;
END WHILE;
END
I hope it will help someone in the future like it helped me
我希望它会帮助将来的某个人，就像它对我的帮助一样
回答by Mahabubur Rahman Masum
If you have common field in both table then it's so easy !....
如果您在两个表中都有共同的字段，那就太容易了！....
Table-1 = table where you want to update. Table-2 = table where you from take data.
表 1 = 要更新的表。表 2 = 您从中获取数据的表。
- make query in Table-1 and find common field value.
- make a loop and find all data from Table-2 according to table 1 value.
- again make update query in table 1.
- 在表 1 中进行查询并找到公共字段值。
- 进行循环并根据表 1 的值从表 2 中查找所有数据。
- 再次在表 1 中进行更新查询。
$qry_asseet_list = mysql_query("SELECT 'primary key field' FROM `table-1`");
$resultArray = array();
while ($row = mysql_fetch_array($qry_asseet_list)) {
$resultArray[] = $row;
}
foreach($resultArray as $rec) {
$a = $rec['primary key field'];
$cuttable_qry = mysql_query("SELECT * FROM `Table-2` WHERE `key field name` = $a");
$cuttable = mysql_fetch_assoc($cuttable_qry);
echo $x= $cuttable['Table-2 field']; echo " ! ";
echo $y= $cuttable['Table-2 field'];echo " ! ";
echo $z= $cuttable['Table-2 field'];echo " ! ";
$k = mysql_query("UPDATE `Table-1` SET `summary_style` = '$x', `summary_color` = '$y', `summary_customer` = '$z' WHERE `summary_laysheet_number` = $a;");
if ($k) {
echo "done";
} else {
echo mysql_error();
}
}
