Update Join Query Syntax Error

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 Join Query Syntax Error

Author

Topic

Pom Grewal
Starting Member

14 Posts

Posted - 2011-11-22 : 08:56:12

Hi Forum

Im working on a an update to two table that are joined. The select query works fine and gives my the desired results, however when I try and run the update, I get an "Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 't1'." error message. Below is my select and update queries. i hope someone can help me.

select t1.[Document No_],
t1.[No_],
t1.[Posting Type],
t2.[Order Date],
t1.[Posting Type],
t1.[Business with Name],
t1.[Business-with No_],
t2.[Task-at Name],
t1.[Amount],*
from DHL.dbo.[DHL Waste$Waste Management Line] t1
left join DHL.dbo.[DHL Waste$Waste Management Header] t2
on t2.[No_] = t1.[Document No_]
where t2.[Business-with Name] like 'DHL Diageo%'
and t1.[Business-with No_] like 'CU-000465'
and t2.[Task-at Name] like 'DHL Diageo%'
and t1.[Sub Contract No_] = '07'

update DHL.dbo.[DHL Waste$Waste Management Line] t1
left join DHL.dbo.[DHL Waste$Waste Management Header] t2
on t2.[No_] = t1.[Document No_]
set t2.[Business-with No_] = 'CU-000573',
t2.[Business-with Name] = 'DHL Diageo - Pepsico',
t2.[Task-at Name] = 'DHL Diageo - Pepsico',
t1.[Sub Contract No_] = '01',
t1.[Business-with No_] = 'CU-000573',
--t1.[Business-with Name] = 'DHL Diageo - Pepsico',
--t1.[Post-with No_] = 'CU-000573',
--t1.[Invoice-with No_] = 'CU-000573'
t1.[Contract No_] = 'WC-000573'
where t2.[Business-with Name] like 'DHL Diageo%'
and t1.[Business-with No_] like 'CU-000465'
and t2.[Task-at Name] like 'DHL Diageo%'
and t1.[Sub Contract No_] = '07'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 09:58:41

update should be likw


update  t2
set t2.[Business-with No_] = 'CU-000573',
t2.[Business-with Name] = 'DHL Diageo - Pepsico',
t2.[Task-at Name] = 'DHL Diageo - Pepsico'
from DHL.dbo.[DHL Waste$Waste Management Header] t2
inner join DHL.dbo.[DHL Waste$Waste Management Line] t1
on t2.[No_] = t1.[Document No_]
where t2.[Business-with Name] like 'DHL Diageo%'
and t1.[Business-with No_] like 'CU-000465'
and t2.[Task-at Name] like 'DHL Diageo%'
and t1.[Sub Contract No_] = '07'

EDIT: removed t1 columns as this needs to be in a separate update

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-22 : 10:06:42

this won't work because of trying to update t1 and t2 at the same time.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 10:09:40

quote:
Originally posted by webfred

this won't work because of trying to update t1 and t2 at the same time.

No, you're never too old to Yak'n'Roll if you're too young to die.

ha

didnt notice his column list

have edited it now

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources