Update Table from 2nd table with 2 column join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update Table from 2nd table with 2 column join

Author

Topic

Fishwagon
Starting Member

10 Posts

Posted - 2014-07-23 : 15:19:15

Hello all,

I'm trying to update a column in a table using the column from another table. The column name being updated is named DocumentType and the column from the 2nd table that will supply the value is AltDocType. Not all rows in Table 1 are being updated, only a small subset. Table 2 contains all the updates needed.

The first table is linked to the 2nd table by 2 columns, BatchName and BatchPg#

While I can select the matching columns, I can't figure out how to modify one with the other. This query returns the matching columns I need to update:

SELECT A.DocumentType, R.AltDocType FROM CorrectedBenchmarkV1 A, Update R
where A.Batch_Name = R.[BatchName]
and A.Seq = R.BatchPg#

Results:
DocumentType AltDocType
Financial Statement Profit and Loss Statement
Foreclosure Statement Default Correspondence
Foreclosure Statement Default Correspondence
Foreclosure Statement Default Correspondence
Foreclosure Statement Borrower and Property Info

Here is the query I was trying to come up with to do the update:

UPDATE CorrectedBenchmarkV1
INNER JOIN Update RU1
ON CorrectedBenchmarkV1.Batch_Name = RU1.BatchName
INNER JOIN Update RU2
On CorrectedBenchmarkV1.Seq = RU2.BatchPg#
SET CorrectedBenchmarkV1.DocumentType = Update.AltDocType

However that is not working:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'INNER'.

I thought I had to do two different joins using a different alias for each column in the UPDATE table.

Any ideas?

Thanks and have a great day...

Rich

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-07-23 : 15:31:13

You almost got it. Try:

UPDATE V1
SET DocumentType = RU1.AltDocType
FROm CorrectedBenchmarkV1 V1 
INNER JOIN Update RU1
ON V1.Batch_Name = RU1.BatchName AND On V1.Seq = RU1.BatchPg#

djj

Fishwagon
Starting Member

10 Posts

Posted - 2014-07-23 : 15:38:45

Thank you sir, that did it....

Appreciate it...

Thanks and have a great day...

Rich

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources