CODEWITHSUNDEEP
sql
user3293688
user3293688

Reputation: 3

How to update a column in a table from another column in another table

Please I have a table1 that has one of its columns named price. And I have another table2 that has one of its columns named price. I want to update column price in table2 with the value in column price in table1.

I tried this script but it didn't update it.

BEGIN 

UPDATE [WAC].[dbo].[IV00101]
   SET 
      [STNDCOST] = (Select LISTPRCE from IV00105 WHERE IV00101.ITEMNMBR = 'IV00105.ITEMNMBR')
      WHERE IV00101.ITEMNMBR = 'IV00105.ITEMNMBR'

END

please help me out.

Thank you

Upvotes: 0

Views: 59

Answers (1)

Raj More
Raj More

Reputation: 48018

Your issue is probably because you have enclosed IV00105.ITEMNMBR in quotes. This is causing the database engine to search for an item named "IV00105.ITEMNMBR" rather than join the two tables and pick up appropriate rows.

This will work (assuming table names based on the query you provided):

UPDATE [WAC].[dbo].[IV00101]
SET 
    [STNDCOST] = LISTPRCE 
From IV00105 
WHERE [WAC].[dbo].[IV00101].ITEMNMBR = IV00105.ITEMNMBR

Upvotes: 2

Related Questions