T-SQL tables updation

Question

0.00/5 (No votes)

See more:

I have an item_data table with unique ID's 'item_id' which is a unique identifier column and item name. I have another table item_data_new with new ID's and the item name is the same as in table item_data. My aim is to update all the IDs of item in the item_data table with the id's of item_data_new where the item name matches. This code is written in T-SQL.The below code is giving error:
T1.item_id could not be bound.

What I have tried:

UPDATE item_data
SET T1.item_id= T2.item_id

FROM item_data T1 JOIN item_data_new T2
ON T1.name= T2.name

Posted 20-Sep-21 1:01am

Member 15352290

Updated 20-Sep-21 1:28am

Add a Solution

2 solutions

Solution 2

The columns on the left-hand side of the assignment in an UPDATE can only refer to the table being updated, so you don't need to specify the table name or alias:

SQL

UPDATE T1
SET item_id = T2.item_id
FROM item_data As T1 
INNER JOIN item_data_new As T2
ON T1.name = T2.name;

UPDATE (Transact-SQL) - SQL Server | Microsoft Docs[^]

Posted 20-Sep-21 1:28am

Richard Deeming

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2021-09-20T01:15:00

Solution 1

T1 is not available as a pseudonym in the SET. Use the full table name instead:

SQL

UPDATE item_data
SET Item_Data.item_id= T2.item_id

FROM item_data T1 JOIN item_data_new T2
ON T1.name= T2.name

Posted 20-Sep-21 1:15am

OriginalGriff