CODEWITHSUNDEEP
sql-server
MattZ71
MattZ71

Reputation: 11

Update using right function in Where statement

I am trying to update one table from another table using the last 4 digits of a column. Below is what I have that is obviously failing. Using SQL 2012

UPDATE [DB].[dbo].[table1]
SET column1 = (SELECT table2.column1
                FROM table2
                WHERE (dbo.table2.column2 = dbo.table1.column2)
                 AND (dbo.table2.column3 = dbo.table1.column3) 
                 AND (dbo.table2.column4 = dbo.table1.column4) 
                 AND (dbo.table2.right(column5,4) = dbo.table1.right(column5,4))

Upvotes: 1

Views: 251

Answers (1)

Lamak
Lamak

Reputation: 70638

The right way to perform an UPDATE with a JOIN in SQL Server is:

UPDATE t1
SET t1.column1 = t2.column1 
FROM [DB].[dbo].[table1] t1
INNER JOIN dbo.table2 t2
    ON t2.column2 = t1.column2 COLLATE SQL_Latin1_General_CP1_CI_AS
    AND t2.column3 = t1.column3 COLLATE SQL_Latin1_General_CP1_CI_AS
    AND t2.column4 = t1.column4 COLLATE SQL_Latin1_General_CP1_CI_AS
    AND right(t2.column5,4) = right(t1.column5,4) COLLATE SQL_Latin1_General_CP1_CI_AS;

Upvotes: 1

Related Questions