Update column based on matching values in other table in mysql

Question

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

Solution 1

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;

Solution 2

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

Solution 3

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.