CODEWITHSUNDEEP
mysql
Edward144
Edward144

Reputation: 487

MySQL Update Table WHERE column in another is

I have two tables in MySQL and I would like to update a column in one of them to match values from another.

The tables each have a customer and part number column. 

Table 1:
Customer_Name | Part_Number | Demand | Allocation

Table 2:
Customer_Name | Part_Number | Demand

I want to update table 1 to add table 2 demand to the allocation figure in table 1, if the part number and customer both exist in table 2.

A query I have attempted so far:

UPDATE `Packing_Dispatch` SET Allocation = `Packing_Allocation`.Demand WHERE
        Customer_Name = `Packing_Allocation`.Customer_Name AND Part_Number = `Packing_Allocation`.Part_Number

How can I do this?

Upvotes: 1

Views: 57

Answers (1)

Fahmi
Fahmi

Reputation: 37473

You can try below

UPDATE `Packing_Dispatch` 
Join `Packing_Allocation`
SET Allocation = `Packing_Allocation`.Demand 
WHERE Customer_Name = `Packing_Allocation`.Customer_Name AND Part_Number = `Packing_Allocation`.Part_Number

Upvotes: 2

Related Questions