CODEWITHSUNDEEP
mysqldatabase
user10442175
user10442175

Reputation:

Select customer from table A then find customer in table B and insert the customer_id into table B

I am taking old records from excel sheets and moving them into a database.

I have a customers table and an orders table.

The customers table has fields customer_id ---primary key ---auto increment First_Name Last_Name

The orders table has fields: customer_id (empty will be a foreign key for customers table) First_Name (populated) Last_Name (populated)

I need to select the customer_id, first_name and last_name row by row from the customers table then find each order in the orders table with the same name the insert the customer_id from the customers table to the orders table.

There are about 5000 orders and 3000 unique customers and I am trying to assign the customer_id to each order. I can do it manually but it would take a week of Sundays.

Thanks

Upvotes: 0

Views: 320

Answers (1)

PSK
PSK

Reputation: 17943

Your existing table design have issues (like what if two or more customers have same name?), still if you want to update the customer_id based on customer name you can try query like following.

update o
set o.customer_id =c.customer_id 
from orders o
inner join customers c on c.First_Name =o.First_Name and c.Last_Name=o.Last_Name

Upvotes: 1

Related Questions