CODEWITHSUNDEEP
sqlsql-server
user5669450
user5669450

Reputation: 39

The multi-part identifier could not be found

I have a sql code here. I am trying to left join two tables and where there is no match I want to change the status code for the first table to be changed.

Update pbmsys.dbo.mhj_tmp_list 
SET 
    a.status_cd = 'I'
FROM 
   pbmsys.dbo.mhj_tmp_list a 
LEFT JOIN 
    staging.dbo.delity_List1 b
ON 
  a.drug_id = b.drug_id 
where 
   a.mac_id = 154 and b.drug_id is null

I am getting an error

"The multi-part identifier "a.status_cd" could not be bound."

The column is in the table. How do i fix this? am I writing an incorrect code for what I am trying to achieve here?

Thank you!

Upvotes: 1

Views: 562

Answers (1)

Fuzzy
Fuzzy

Reputation: 3810

You have:

Update pbmsys.dbo.mhj_tmp_list 
SET 
    a.status_cd = 'I'
FROM 
   pbmsys.dbo.mhj_tmp_list a 
LEFT JOIN 
    staging.dbo.delity_List1 b
ON 
  a.drug_id = b.drug_id 
where 
   a.mac_id = 154 and b.drug_id is null

You should have:

Update a --<-- this is the alias of the table pbmsys.dbo.mhj_tmp_list
SET 
    a.status_cd = 'I'
FROM 
   pbmsys.dbo.mhj_tmp_list a --<-- this is the alias of the table pbmsys.dbo.mhj_tmp_list
LEFT JOIN 
    staging.dbo.delity_List1 b
ON 
  a.drug_id = b.drug_id 
where 
   a.mac_id = 154 and b.drug_id is null

The reason for the error is you have given the table pbmsys.dbo.mhj_tmp_list the alias a in the FROM part of the UPDATE and the column you are updating is a.status_cd however in the UPDATE section you have pbmsys.dbo.mhj_tmp_list rather than the alias a

Upvotes: 1

Related Questions