MySQL 根据mysql中其他表中的匹配值更新列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12650575/
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
Update column based on matching values in other table in mysql
提问by Uma
I have two tables calendar and customer table. Calendar table have a "customer" column which has customer table "ID" as value. But unfortunately, this calendar customer field value was wrongly populated with other values. Both tables have these common fields Date, SeatingID and BusID. How to update the calendar table customer field based on these common fields?.
我有两个表日历和客户表。日历表有一个“客户”列，其中有客户表“ID”作为值。但不幸的是，此日历客户字段值错误地填充了其他值。两个表都有这些公共字段 Date、SeatingID 和 BusID。如何根据这些常用字段更新日历表客户字段？
Below is the structure of both tables.
下面是两个表的结构。
Customer Table
客户表
calendar Table
日历表
采纳答案by Uma
update calendar ca left join customer c
on c.DateofTravel=ca.Date and c.SeatingID=ca.SeatingID and c.BusID=ca.BusID
set
ca.Customer=c.ID;
回答by Mahmoud Gamal
You can UPDATEthe Customerfield of the second table Calendarfrom the first table Customerby JOINing the two tables like so:
您可以通过ing 两个表来从第一个表中获取第二个表UPDATE的Customer字段，如下所示：CalendarCustomerJOIN
UPDATE calendar c1
INNER JOIN Customer c2 ON c1.SeatingID = c2.SeatingID AND c1.BusID = c2.BusID
SET c1.Customer = c2.ID --or SET c1.Customer = c2.PassengerName or whatever you want.
In the SETclause, you can set the column you wish to update, and you can also JOINthe two tables based on any predicate, I used c1.SeatingID = c2.SeatingID AND c1.BusID = c2.BusID, but you can choose what is suitable for your needs.
在SET子句中，你可以设置你想要更新的列，也可以JOIN根据任何谓词来设置两个表，我用的是c1.SeatingID = c2.SeatingID AND c1.BusID = c2.BusID，但你可以选择适合你需要的。
Here is a SQL Fiddle demo
这是一个 SQL Fiddle 演示
回答by Robert
Try this code:
试试这个代码：
UPDATE calendar cal, customer cust
SET cal.Customer = cust.ID
where cal.SeatingID = cust.SeatingID
and cal.BusID = cust.BusID
and cal.DATE = cust.DateOfTravel;
SQL FiddleDEMO
SQL小提琴演示
Hereis link to more informations abaout update.
这是 abaout 更多信息的链接update。
回答by Sunny S.M
Use this query it will help you to updated table column from another table column:
使用此查询将帮助您从另一个表列更新表列：
UPDATE tableName1 AS tb1
INNER JOIN tableName2 AS tb2
ON (tb1.columnName= tb2.columnName)
SET tb1.updatedColumn = tb2.updatedColumnValue
WHERE ADD HERE CONDITION IF REQUIRED
