Update a column of a table using another column of the same table

Question

I have 2 tables vehicle_table and vehicle_group_table

• vehicle_table --> Has columns --> vehicleid,groupname,groupid
• vehicle_group_table --> has columns --> groupid,groupname

I want to update my vehicle_table's groupid column by joining vehicle_group_table on its common groupname column

UPDATE vehicle_table 
    SET vehicle_table.groupid = vehicle_group_table.groupid
    WHERE vehicle_table.groupname = vehicle_group_table.groupname

But this seems to be not working.

Answer 1

UPDATE
  V
SET
  groupid = VG.groupid
FROM
  vehicle_table V
  JOIN
  vehicle_group_table VG ON V.groupname = VG.groupname

You need to correlate the 2 tables via a JOIN. There are other ways with subqueries etc

Note: don't use aliases in destination/target columns in the SET clause as suggested in other answers. It fails in SQL Server Not in SQL Server 2012 so I think behaviour changed

For a more complicated setup: SQL update query using joins

Answer 2

You can apply the following syntax in your scenario:

UPDATE TargetTable

FROM SourceTable

SET TargetTableField = SourceTable.SourceTableField

WHERE TargetTable.Field = TargetTable.Field;

Answer 3

Try this :-

Update vt
Set vt.groupiid = vgt.groupid
from vehicle_table vt
inner join vehicle_group_table vgt
on vt.groupname = vgt.groupname

Answer 4

Try it

    UPDATE A SET A.groupid = B.groupid 
    FROM vehicle_table A INNER JOIN vehicle_group_table B ON 
    A.groupname = B.groupname

Answer 5

Try this :

UPDATE a set a.groupid =b.groupid

from vehicle_table a
inner join 
vehicle_group_table b

on a.groupname = b.groupname