CODEWITHSUNDEEP
sqlsql-serversql-update
Bert Vandamme
Bert Vandamme

Reputation: 345

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

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!

Upvotes: 2

Views: 2508

Answers (2)

Martin Smith
Martin Smith

Reputation: 453990

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.

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103585

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?

Upvotes: 4

Related Questions