[SOLVED] Update SQL table using selection from another table

Update SQL table using selection from another table

I want to update the values of a SQL table based on the value from another table where the two tables have a common ID.

When I run my script it says it work. But when I check the table that should have been updated value it still has the old/original value.

I based my script on an article from this site (how-do-i-update-from-a-select-in-sql-server) but because SQL is telling be the script ran with no errors and 2 rows where updated I can't seem to figure out what I did wrong.

UPDATE    Table A
SET       Table A.StatusID = 2
FROM      Table B INNER JOIN
          Table A ON Table B.ID = Table A.ID
WHERE     (Table B.ApprovalState = 2)

Solution

Your query should be writen as:

UPDATE    A
SET       StatusID = 2
FROM      TableB AS B INNER JOIN
          TableA AS A ON B.ID = A.ID
WHERE     B.ApprovalState = 2