Update Joining 3 Tables - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Update Joining 3 Tables

Author

Topic

jej1216
Starting Member

27 Posts

Posted - 2009-02-03 : 19:10:40

I have a simple update with one join that works fine. This works:
UPDATE PS_WTHD_TRXN_TBL
SET WTHD_BASIS_AMT = T1.[PYMNT_AMT]
FROM PS_PAYMENT_TBL T1
JOIN PS_WTHD_TRXN_TBL T2
ON T2.BANK_SETID = T1.BANK_SETID
AND T2.BANK_CD = T1.BANK_CD
AND T2.BANK_ACCT_KEY = T1.BANK_ACCT_KEY
AND T2.PYMNT_METHOD = T1.PYMNT_METHOD
AND T2.PYMNT_ID = T1.PYMNT_ID
AND T2.VENDOR_SETID = T1.REMIT_SETID
AND T2.VENDOR_ID = T1.REMIT_VENDOR
AND T2.PYMNT_DT = T1.PYMNT_DT
WHERE T1.[PYMNT_AMT] IS NOT NULL
AND T1.PYMNT_DT >= '2007-01-01'
AND T1.PYMNT_DT <= '2007-12-31'
AND T1.PYMNT_AMT <> T2.WTHD_BASIS_AMT

I have another update that has to join 3 tables instead of just 2 as above. This 3 join update is not working. Here it is:

UPDATE PS_PYMNT_VCHR_WTHD
SET WTHD_DECL_DATE = T1.[PYMNT_DT]
FROM PS_PAYMENT_TBL T1,PS_WTHD_TRXN_TBL T2
JOIN PS_PYMNT_VCHR_WTHD T3
ON T3.BUSINESS_UNIT = T2.BUSINESS_UNIT
AND T3.PROCESS_INSTANCE = T2.PROCESS_INSTANCE
JOIN PS_WTHD_TRXN_TBL T2
ON T2.BANK_SETID = T1.BANK_SETID
AND T2.BANK_CD = T1.BANK_CD
AND T2.BANK_ACCT_KEY = T1.BANK_ACCT_KEY
AND T2.PYMNT_METHOD = T1.PYMNT_METHOD
AND T2.PYMNT_ID = T1.PYMNT_ID
AND T2.VENDOR_SETID = T1.REMIT_SETID
AND T2.VENDOR_ID = T1.REMIT_VENDOR
AND T2.PYMNT_DT = T1.PYMNT_DT
WHERE T1.[PYMNT_DT] >= '2007-01-01'
AND T1.[PYMNT_DT] <= '2007-12-31'
AND T1.PYMNT_AMT <> T2.WTHD_BASIS_AMT

I get this error:
Server: Msg 1011, Level 15, State 1, Line 8
The correlation name 'T2' is specified multiple times in a FROM clause.

I cannot see examples that I understand on how to do this 3 join statement.

I tried changing the statement to:
.............
JOIN PS_PYMNT_VCHR_WTHD T3
ON T3.BUSINESS_UNIT = T2.BUSINESS_UNIT
AND T3.PROCESS_INSTANCE = T2.PROCESS_INSTANCE
AND T2.BANK_SETID = T1.BANK_SETID
AND T2.BANK_CD = T1.BANK_CD
AND T2.BANK_ACCT_KEY = T1.BANK_ACCT_KEY
AND T2.PYMNT_METHOD = T1.PYMNT_METHOD
AND T2.PYMNT_ID = T1.PYMNT_ID
AND T2.VENDOR_SETID = T1.REMIT_SETID
AND T2.VENDOR_ID = T1.REMIT_VENDOR
AND T2.PYMNT_DT = T1.PYMNT_DT
WHERE T1.[PYMNT_DT] >= '2007-01-01'
AND T1.[PYMNT_DT] <= '2007-12-31'
AND T1.PYMNT_AMT <> T2.WTHD_BASIS_AMT

But I just get a different error, repeated 8 times:
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'T1' does not match with a table name or alias name used in the query.

I know my syntax is wrong - how do I write my JOINs correctly?

TIA,

jej1216

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-03 : 19:15:08

It's because of this part: FROM PS_PAYMENT_TBL T1,PS_WTHD_TRXN_TBL T2

Remove the second table from the FROM part.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jej1216
Starting Member

27 Posts

Posted - 2009-02-03 : 19:20:41

I need to tie that third table in somehow - in order to get the dataset I need to update. How can I do that?

Thanks,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-03 : 19:21:47

But it's already in that query via a JOIN, right here: JOIN PS_WTHD_TRXN_TBL T2

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-03 : 19:22:10

You can't put it in there twice without using different aliases, which I'm sure you don't want anyway.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-03 : 19:25:41

This might be what you want although it's hard to know for sure:

UPDATE T3
SET WTHD_DECL_DATE = T1.[PYMNT_DT]
FROM PS_PYMNT_VCHR_WTHD T3
JOIN PS_WTHD_TRXN_TBL T2
ON T3.BUSINESS_UNIT = T2.BUSINESS_UNIT
AND T3.PROCESS_INSTANCE = T2.PROCESS_INSTANCE
JOIN PS_PAYMENT_TBL T1
ON T2.BANK_SETID = T1.BANK_SETID
AND T2.BANK_CD = T1.BANK_CD
AND T2.BANK_ACCT_KEY = T1.BANK_ACCT_KEY
AND T2.PYMNT_METHOD = T1.PYMNT_METHOD
AND T2.PYMNT_ID = T1.PYMNT_ID
AND T2.VENDOR_SETID = T1.REMIT_SETID
AND T2.VENDOR_ID = T1.REMIT_VENDOR
AND T2.PYMNT_DT = T1.PYMNT_DT
WHERE T1.[PYMNT_DT] >= '2007-01-01'
AND T1.[PYMNT_DT] <= '2007-12-31'
AND T1.PYMNT_AMT <> T2.WTHD_BASIS_AMT

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jej1216
Starting Member

27 Posts

Posted - 2009-02-03 : 19:31:07

I know that to you this sounds simple, but to me I feel like the top of my head is going to blow off.

I need to set the T3 Field value to a field in T1, but I have to JOIN in the T2 table to get from T1 to T3 and to get the correct dataset.

If I remove the T2 table from "FROM PS_PAYMENT_TBL T1,PS_WTHD_TRXN_TBL T2" the there's nothing there to identify what T2 is in the JOIN ONs. This returns errors saying as much:

UPDATE PS_PYMNT_VCHR_WTHD
SET WTHD_DECL_DATE = T1.[PYMNT_DT]
FROM PS_PAYMENT_TBL T1
JOIN PS_PYMNT_VCHR_WTHD T3
ON T3.BUSINESS_UNIT = T2.BUSINESS_UNIT
AND T3.PROCESS_INSTANCE = T2.PROCESS_INSTANCE
JOIN PS_WTHD_TRXN_TBL T2
ON T2.BANK_SETID = T1.BANK_SETID
AND T2.BANK_CD = T1.BANK_CD
AND T2.BANK_ACCT_KEY = T1.BANK_ACCT_KEY
AND T2.PYMNT_METHOD = T1.PYMNT_METHOD
AND T2.PYMNT_ID = T1.PYMNT_ID
AND T2.VENDOR_SETID = T1.REMIT_SETID
AND T2.VENDOR_ID = T1.REMIT_VENDOR
AND T2.PYMNT_DT = T1.PYMNT_DT
WHERE T1.[PYMNT_DT] >= '2007-01-01'
AND T1.[PYMNT_DT] <= '2007-12-31'
AND T1.PYMNT_AMT <> T2.WTHD_BASIS_AMT

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-03 : 19:40:01

Did you see my last post?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jej1216
Starting Member

27 Posts

Posted - 2009-02-03 : 19:48:01

No, I missed that last post - sorry. That works! Thanks! Just so I understand my mistake better - is it because I did not assign T3 on my UPDATE line or is it because of the order of my joins that mine failed?

Thanks again - exploding head avoided.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-03 : 19:51:37

It was mostly an issue with adding PS_WTHD_TRXN_TBL T2 to the query. I always put the table that I am updating in the FROM part and then work my way down.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources