Update statement using a subquery with correlation

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

General SQL Server Forums

New to SQL Server Programming

Update statement using a subquery with correlation

Author

Topic

jr53523
Starting Member

2 Posts

Posted - 2008-04-18 : 10:27:22

Hello

I am new to this forum and pretty new to running queries in SQL Server. I have been doing it for years on an iSeries platform and the following update statement would definitely work in SQL/400....but it does not in SQL Server 2000. Any help would be appreciated.

UPDATE TESTDTA.F0101 X
SET (ABAC07, ABAC12, ABAC28) =
(SELECT AIAC07, AIAC12, AIAC28 FROM TESTDTA.F03012
WHERE AIAN8 = X.ABAN8)
WHERE EXISTS(SELECT AIAC07 FROM TESTDTA.F03012
WHERE AIAN8 = X.ABAN8)

...and here are the errors

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'X'.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'WHERE'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-18 : 10:31:27

Take backup of tables and then try

UPDATE X
SET ABAC07=Y.AIAC07, ABAC12=Y.AIAC12, ABAC28=Y.AIAC28
FROM TESTDTA.F0101 X INNER JOIN TESTDTA.F03012 Y
ON AIAN8 = X.ABAN8
WHERE EXISTS(SELECT AIAC07 FROM TESTDTA.F03012
WHERE AIAN8 = X.ABAN8)

Madhivanan

Failing to plan is Planning to fail

jr53523
Starting Member

2 Posts

Posted - 2008-04-18 : 15:32:39

That worked. Thanks

quote:
Originally posted by madhivanan

Take backup of tables and then try

UPDATE X
SET ABAC07=Y.AIAC07, ABAC12=Y.AIAC12, ABAC28=Y.AIAC28
FROM TESTDTA.F0101 X INNER JOIN TESTDTA.F03012 Y
ON AIAN8 = X.ABAN8
WHERE EXISTS(SELECT AIAC07 FROM TESTDTA.F03012
WHERE AIAN8 = X.ABAN8)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources