Update all column values in a table with a column value of another table (related by a foreign key)

Question

Hi,

I'm trying to populate a new column in a table with data residing in another table's column.

    UPDATE  dbo.PackageSessionNodes
    SET     Created_By = 
               (select userid from dbo.PackageSessions, dbo.PackageSessionNodes 
                where  dbo.PackageSessions.PackageSessionId 
                        = dbo.PackageSessionNodes.PackageSessionId)

The update fails because of the subquery retuns more than one value. I'm obviously a sql noob, so any help would be appreciated.

Thx!

Answer 1

I think you are looking for this syntax:

update dbo.PackageSessionNodes set Created_By = p.userid
from dbo.PackageSessionNodes pn
left join dbo.PackageSessions p on p.PackageSessionId = pn.PackageSessionId

I assume a PackageSessionNode can only have one PackageSession?

Answer 2

Your original query is doing a cartesian join which might explain the multiple results

Replace

dbo.PackageSessions , dbo.PackageSessionNodes 

with

  dbo.PackageSessions

and add DISTINCT

UPDATE  dbo.PackageSessionNodes
SET     Created_By = 
           (select DISTINCT userid from dbo.PackageSessions 
          where  dbo.PackageSessions.PackageSessionId = 
             dbo.PackageSessionNodes.PackageSessionId)

This will still fail if you have multiple different results being returned in which case you need to define which one you want to use.