Update statement with join condition

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

Transact-SQL (2005)

Update statement with join condition

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-11-05 : 03:59:25

Dear All,
I am new to T-SQL.please help me in updating the table a by joining table b.
Here is the my query.

UPDATE TES_TRANSACTIONS TT
SET TT.EDM_BATCH=TTH.EDM_Batch,
TT.EDM_RCVD_Date=TTH.EDM_Rcvd_Date,
TT.EDM_Creation_Date=TTH.EDM_Creation_Date,
TT.EDM_TES_Post_Date=TTH.EDM_TES_Post_Date
FROM TES_TRANSACTIONS TT
INNER JOIN TES_TRANSACTIONS_HISTORY TTH
ON TT.Encounter=TTH.Encounter
AND TT.Transaction=TTH.Transaction

correct me i am getting the syntax error.

Thanks In advance,
Ganga

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-11-05 : 04:01:50

Now it will work!

UPDATE TT
SET TT.EDM_BATCH=TTH.EDM_Batch,
TT.EDM_RCVD_Date=TTH.EDM_Rcvd_Date,
TT.EDM_Creation_Date=TTH.EDM_Creation_Date,
TT.EDM_TES_Post_Date=TTH.EDM_TES_Post_Date
FROM TES_TRANSACTIONS TT
INNER JOIN TES_TRANSACTIONS_HISTORY TTH
ON TT.Encounter=TTH.Encounter
AND TT.Transaction=TTH.Transaction

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-11-05 : 04:14:07

No Senthil...stilli am getting the syntax error.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-05 : 04:34:54

wt error ur getting can u post the error message

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-11-05 : 04:37:01

Hi,
I am getting this error:
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'Transaction'.

please help.If i remove this condition it will work AND TT.Transaction=TTH.Transaction
But if i include this condition with On --> it throus an error.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-05 : 04:39:41

UPDATE TT
SET TT.EDM_BATCH=TTH.EDM_Batch,
TT.EDM_RCVD_Date=TTH.EDM_Rcvd_Date,
TT.EDM_Creation_Date=TTH.EDM_Creation_Date,
TT.EDM_TES_Post_Date=TTH.EDM_TES_Post_Date
FROM TES_TRANSACTIONS TT
INNER JOIN TES_TRANSACTIONS_HISTORY TTH
ON TT.Encounter=TTH.Encounter
AND TT.[Transaction]=TTH.[Transaction]

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2009-11-05 : 04:43:27

Hi bklr,

Thanks a lot it works for me..i think as transaction is keyword we need to include it in a square bracket.
Thanks a lot...

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-05 : 04:44:35

ya welcome........

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources