CODEWITHSUNDEEP
sqlsql-servert-sqlwhere-clausesql-insert
Buggieboy
Buggieboy

Reputation: 4696

INSERT with subquery can't bind reference to target table

I'm trying to consolidate two columns in a table into a foreign key reference to another table that contains those same two columns. I first want to do something like:

INSERT INTO dbo.Securities (SubIndustID) 
    SELECT ID FROM dbo.SubIndust
        WHERE dbo.SubIndust.GICS_Sector = dbo.Securities.GICS_Sector
        AND dbo.SubIndust.SubIndustry = dbo.Securities.SubIndustry;

I get an error message for each reference in the sub-query to a column in the destination table:

The multi-part identifier "dbo.Securities.GICS_Sector" could not be bound.

The multi-part identifier "dbo.Securities.SubIndustry" could not be bound.

Why is the correct way to make this foreign key substitution?

Upvotes: 0

Views: 193

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271211

You need to mention the table in the from clause if you want to use it elsewhere in the query. This would be a more syntactically correct version:

INSERT INTO dbo.Securities (SubIndustID) 
    SELECT i.ID
    FROM dbo.SubIndust i JOIN
         dbo.Securities s
         ON i.GICS_Sector = s.GICS_Sector AND
            i.SubIndustry = s.SubIndustry;

However, I suspect that you really want an update:

update s
    set s.SubIndustID = i.Id
    from dbo.Securities s JOIN
         dbo.SubIndust i
         on i.GICS_Sector = s.GICS_Sector AND
            i.SubIndustry = s.SubIndustry;

Upvotes: 2

Related Questions