CODEWITHSUNDEEP
sqlsql-server
ethan
ethan

Reputation: 11

The multi-part identifier could not be bound update column

UPDATE country
SET country.postcode = (SELECT t.postcode
                        FROM Patch_Country t
                        WHERE t.Rows = 2 AND tll.id = t.id),
    country.state = (SELECT t.state
                     FROM Patch_Country t
                     WHERE t.Rows = 1 AND tll.id = t.id)
FROM country tla WITH (NOLOCK)
INNER JOIN country2 tll ON tla.id = tll.id
WHERE tll.code = tla.code

I tried to add inner join at the rows still showing error I'm not sure why this error is showing.

The multi-part identifier could not be bound

Upvotes: 0

Views: 734

Answers (2)

jayesh sheta
jayesh sheta

Reputation: 181

I think the issues of this error is , you have used your table abbreviations incorrectly

try below code

UPDATE country SET postcode = (SELECT t.postcode FROM Patch_Country t WHERE t.Rows = 2 AND tll.id = t.id), state = (SELECT t.state FROM Patch_Country t WHERE t.Rows = 1 AND tll.id = t.id) FROM country tla WITH (NOLOCK) INNER JOIN country2 tll ON tla.id = tll.id WHERE tll.code = tla.code

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try below:

UPDATE country
SET country.postcode = (CASE WHEN t.Rows = 2 THEN t.postcode END),
    country.state = (CASE WHEN t.Rows = 1 THEN t.state END)
FROM country tla 
INNER JOIN country2 tll ON tla.id = tll.id AND tll.code = tla.code
INNER JOIN Patch_Country t ON tll.id = t.id

Upvotes: 0

Related Questions