Sql query search the column data from one table and another table column

Question

Sql query search the column data from one table and another table column

ShawnBryan

SSCrazy

Points: 2157
More actions
June 10, 2016 at 4:05 am

#326869

I have a database name called Scanner with the following two table.
First table name dbo.1 and first column (InvoiceNum) and second column (CustomerNum)
Second table name dbo.2 and first column (InvoiceNum) and second column (CustomerNum)
I would like to match the both table invoicenum and copy the customernum from first table to second table customernum.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Sounds like a job for the MERGE statement (although make sure you understand the pitfalls before you use it). I can't be any more specific, though, without table DDL (CREATE TABLE statements), sample data (INSERT statements) and expected results.

John

ShawnBryan SSCrazy Points: 2157 More actions · Answer 2

table 1 looks like,

InvoiceNum CustomerNum InvoiceDate

12027637439004283 07.03.2016

12030208629004283 08.05.2016

12028088899004283 19.03.2016

12025513219004283 04.01.2016

12028559549004283 29.03.2016

12026634299004283 08.02.2016

12026920619004283 16.02.2016

table 2 look like,

InvoiceNum CustomerNum InvoiceDate

1203020862 * 08.05.2016

I want search customernum from table1 with matching the both table invoicenum and insert into table2 customernum.

Thanks...

joeroshan SSChampion Points: 10377 More actions · Answer 3

If you provide table definition as something like below, it is easier to help

Create table [dbo].[1]

(

InvoiceNum INt,

customernum INt,

InvoiceDate Datetime

)

Go

Create table [dbo].[2]

(

InvoiceNum INt,

customernum INt,

InvoiceDate Datetime

)

I am assuming your table definition above.

You can use a simple update as

Update T SET T.customernum=S.customernum

OUTPUT inserted.*,deleted.*

FROM [dbo].[2] T JOIN

[dbo].[1] S

on T.InvoiceNum=S.InvoiceNum

Or Merge

as

Merge [dbo].[2] T USING

[dbo].[1] S

on T.InvoiceNum=S.InvoiceNum

WHEN MATCHED THEN

UPDATE SET T.customernum=S.customernum

OUTPUT $action,inserted.*,deleted.*;

here we are assuming your expected output also.

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

ShawnBryan SSCrazy Points: 2157 More actions · Answer 4

Hi thanks its worked.

SELECT dbo.ScannedDocs.InvoiceNum AS S

FROM dbo.ScannedDocs;

SELECT dbo.1.InvoiceNum AS T

FROM dbo.1;

Update T SET T.customernum=S.customernum

OUTPUT inserted.*,deleted.*

FROM dbo.ScannedDocs T JOIN

dbo.[1] S

on T.InvoiceNum=S.InvoiceNum