Geek asks and answers

Refresh column based on matching values ​​in another table in mysql

I have two calendar tables and a customer table. There is a client column in the calendar table that has a client identifier of "ID" as a value. But, unfortunately, this value of the calendar client field was incorrectly populated with other values. Both tables share these common Date, MaleID, and BusID fields. How to update the client field of a calendar table based on these common fields ?.

The structure of both tables is shown below.

Customer table enter image description here

Calendar table

enter image description here

+5
source share
4 answers
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;
+1
source

UPDATE Customer Calendar Customer JOIN :

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.

SET , , JOIN , c1.SeatingID = c2.SeatingID AND c1.BusID = c2.BusID, , .

SQL Fiddle

+5

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 a link to additional information abaout update.

+1
source

This query will help you update a 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
0
source

More articles:


All Articles