SQL Update values in table A with multiple values from table B

Question

I'm trying to update products' prices in table A with prices from table B based on their product code.

There are about 50,000 products in table A but only 200 in table B. So I want those 200 prices in table B to replace the prices of those products in table A by matching the product code in both tables.

Can anyone advise me on how to go about doing it?

My database is on sql server. I tried to get some references from sites and edited my sql statement to be this. UPDATE dbo.Shelflabel_Pricechange_Master SET [Unit Price] = (SELECT t2.[Unit Price] FROM dbo.shelflabel_pricechange t2 WHERE dbo.Shelflabel_Pricechange_Master.Plu = t2.PLU) WHERE EXISTS (SELECT 1 FROM dbo.shelflabel_pricechange t2 WHERE dbo.Shelflabel_Pricechange_Master.Plu = t2.PLU) — user2818108, Sep 26 '13 at 06:08

score 1 · Answer 1 · answered Sep 26 '13 at 06:11

1

UPDATE  TableA
SET     TableA.ProductPrice= TableB.price
FROM    TableA
INNER JOIN  TableB
ON  TableA.productCode = TableB.productCode

answered Sep 26 '13 at 06:11

Nadeem_MK

7,533
7
50
61

score 0 · Answer 2 · edited May 23 '17 at 10:24

0

You can use inner join update for MS Sql server(reference: Update a table using JOIN in SQL Server?). Please try below query:

UPDATE 
 TableA
SET TableA.Price = TableB.Price
FROM 
 TableA, TableB
WHERE 
 TableA.ProductCode = TableB.ProductCode

edited May 23 '17 at 10:24

Community

1
1

answered Sep 26 '13 at 06:09

TechDo

18,398
3
51
64

Isn't it preferable to use explicit join syntax rather than the old FROM Table,Table. Only reason i upvoted @Nadeem_MK answer. – ZeRaTuL_jF Apr 15 '15 at 16:17

score 0 · Answer 3 · answered Sep 26 '13 at 06:24

0

try this command in mysql

update A t
set cost=(select cost from B where id=t.id)
where t.id in (select id from B)

answered Sep 26 '13 at 06:24

Dev

3,410
4
17
16

SQL Update values in table A with multiple values from table B

3 Answers3

try this command in mysql