July 2, 2008 at 4:21 am
Query 1
Update DIRXMLUSER
SET JOBTITLE = (SELECT SHEET1$.[JOB TITLE] FROM SHEET1$) S
WHERE S. STAFFCODE = STAFFCODE
Query 2
UPDATE DIRXMLUSER
SET DIRXMLUSER.JOBTITLE = SHEET1$.[JOB TITLE]
FROM DIRXMLUSER, SHEET1$
WHERE (DIRXMLUSER.STAFFCODE) = SHEET1$.STAFFCODE)
Can anyone tell me why query 1 fails and query 2 succeeds on a sql 2000 box
July 2, 2008 at 5:25 am
What is the error message you are getting with Query 1 ?
try writing query 1 like this
Update DIRXMLUSER
SET JOBTITLE = (SELECT SHEET1$.[JOB TITLE] FROM SHEET1$ S
WHERE S. STAFFCODE = STAFFCODE)
July 2, 2008 at 5:34 am
query 1 's error said incorrect syntax near S - it refused to use an alias
your version of query 1 also generated errors with the use of the alias.
My SQL knowledge suggests that logically the two querys should give the same result, but I dont understand the reason for query 1's failure.
As with all simple syntax errors it looks correct to me but just doesnt work
July 2, 2008 at 5:43 am
Your first query would have thrown an error since the second ')' was in the wrong place.
also try removing the space after 's. '
July 2, 2008 at 5:48 am
The space after the S is a typo putting it into the forum post.
The structure of query 1 works on a SQL 2005 update statement with the parenthesis placed in the same place on an update query into a different database.
This is why I am bugged about it not working
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply