Update column based on matching values in other ta

问题:

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?.

Below is the structure of both tables.

Customer Table

calendar Table

回答1:

You can UPDATE the Customer field of the second table Calendar from the first table Customer by JOINing the two tables like so:

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 SET clause, you can set the column you wish to update, and you can also JOIN the 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.

Here is a SQL Fiddle demo

回答2:

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 Fiddle DEMO

Here is link to more informations abaout update.

回答3:

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;

回答4:

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