How to set sql field value equal to another value in the same table

Question

I have a table that contains values for different locations where the location id and the item number combination form the primary key. A lot of the items are the same in both locations when it comes to cost etc. I am needing to fix one locations information that got changed by a user somehow. I am looking for advice on how to set the value for location 1 for item 1 to be the same as location 2 item 1. I know how to do this if the value is in a different table, but I am not sure how to accomplish this when the values are in the same table. Any advice will be greatly apprciated

Answer 1

I think you're looking for this:

UPDATE table SET col1 = col2

Sets "col1" to the value of "col2" in all rows of the table "table"

Answer 2

I think you need this:

UPDATE l1
SET COST = l2.COST
FROM TableA l1
INNER JOIN TableA l2 ON l2.Location = 2 AND l1.item = l2.item
WHERE l1.location = 1 and l1.item = 1