CODEWITHSUNDEEP
sql-serversql-update
Jesse Smith
Jesse Smith

Reputation: 963

SQL Update with string concatenation

I am porting an Access database to SQL Server, and came across the following:

UPDATE 
[tableA], 
[tableB], 
[tableC] 
SET [tableC].STATION_NB = [tableB]!Station
WHERE ((([tableA]![FirstOfCOMPONENT NUMBER] & [tableB]![COMPONENT NUMBER]) Like [tableC]![MOTOR_AND_PART]) 
    AND (([tableC]![PART]) Like [tableB]![COMPONENT NUMBER]));

The names of the tables have been changed, to protect the guilty. This was apparently built to make a unique list of the combination of all motors, their unique parts, and the station number where the parts were attached to the motor. Of course, this is nowhere near OK for T-SQL code, so here's my attempt to fix it for the transition:

UPDATE [tableC]
SET [tableC].[STATION_NB] =
    (SELECT b.Station
    FROM [tableB] b
    WHERE ((([tableA].[FirstOfCOMPONENT NUMBER] + b.[COMPONENT NUMBER]) Like [tableC].[MOTOR_AND_PART]) 
        AND (([tableC].[PART]) Like b.[COMPONENT NUMBER])))

When I run that, SSMS complains that The multi-part identifier "tableA.FirstOfCOMPONENT NUMBER" could not be bound. I think this is because I didn't do a JOIN. However, I'm not interested in [tableA].[FirstOfCOMPONENT NUMBER], I'm interested in the concatenation result. but how would I go about doing a JOIN to bring tableA into the query properly?

Upvotes: 0

Views: 232

Answers (1)

Sonam
Sonam

Reputation: 3466

The below query is just an idea of what you would like to do here. You can replace the "ColName" with the column names which are common in tableA and tableB:

Update C
Set C.[STATION_NB]=B.STATION
FROM
    tableA A
join
    tableB B on A.<Colname>=B.<Colname>
join
    tableC C on C.PART=B.[COMPONENT NUMBER]
where
    A.[FirstOfCOMPONENT NUMBER] + B.[COMPONENT NUMBER])= [tableC].[MOTOR_AND_PART]

Upvotes: 1

Related Questions