CODEWITHSUNDEEP
sql-serversql-update
Sherendeep Kaur
Sherendeep Kaur

Reputation: 53

SQL Server : update join statement of two tables with multiple columns from existing table to match

I need to update an existing table (First) with a new column (Ticket_No_Calc). Basically, there are two columns (Doc_Header and Doc_No) that I have to lookup/refer to in the first table against the second table (Doc_No) column.

The desired column Ticket_No_Calc result is retrieved from the column Ticket_No.

Below is the screenshot of sample data of the first table

First Table

and this is the screenshot of sample data of the second table

Second Table

Currently, in Excel, I use the following formula to get the desired column in the existing table 

=IFERROR(VLOOKUP(FIRST!B2,'SECOND'!A:B,1,0),(VLOOKUP(FIRST!C2,'SECOND'!A:B,1,0)))

I am not sure how to do this in SQL. I know that I can get this done in SQL with the following formula if I only have to refer to one column Doc_Header:

UPDATE A 
SET A.Ticket_No_Calc = B.Ticket_No 
FROM First AS A 
LEFT JOIN Second AS B ON A.Doc_Header = B.Doc_NO ;

But I need to refer to two columns in the existing table. I have been researching for sometimes to get the correct command but I can't seem to be able to crack it.

Upvotes: 0

Views: 176

Answers (3)

Somendra Kanaujia
Somendra Kanaujia

Reputation: 824

Try this:

UPDATE A 
SET A.Ticket_No_Calc = B.Ticket_No 
FROM First AS A ,Second AS B where A.Doc_Header = B.Doc_NO or A.Doc_NO = B.Doc_NO

Upvotes: -1

DarkRob
DarkRob

Reputation: 3833

As per the sample data provided this is query seems incorrect. But as per the comments mentioned in your question it is better to perform this in 2 steps.

UPDATE A 
SET A.Ticket_No_Calc = B.Ticket_No 
FROM First AS A 
LEFT JOIN Second AS B ON A.Doc_Header = B.Doc_NO ;


UPDATE A 
SET A.Ticket_No_Calc = B.Ticket_No 
FROM First AS A 
LEFT JOIN Second AS B ON A.DOC_NO=B.DOC_NO
WHERE A.Doc_Header <> B.Doc_NO

Upvotes: 0

Ajay
Ajay

Reputation: 774

Following statement may help

UPDATE A 
SET A.Ticket_No_Calc = B.Ticket_No 
FROM First AS A 
INNER JOIN Second AS B ON (A.Doc_Header = B.Doc_NO) OR (A.Doc_NO = B.Doc_NO) ;

Upvotes: 4

Related Questions