467,926 Members | 1,521 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,926 developers. It's quick & easy.

Using Where..IN in Update query.. not working for some reason

Hi folks,

Hopefully this is a simple fix, but I keep getting Syntax error with
this statement in an MS SQL DTS statement and in Query Analyzer:

Update A Set A.deptcode = A.deptcode,
A.type = A.Type,
a.TotalExpenseUnit = (a.LaborExpenseUnit + a.OtherExpenseUnit)
Where a.Type in ('FYTD04', 'Prior Year','Budget')
From Data_Unsorted A Join Data_Unsorted B On
A.deptcode = B.deptcode and A.type = B.Type

Below is the error from Query Analyzer:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'From'.

Where do I place my Where..In statement since I only want to limit the
Update to run for items where a.type is FYTD04, Prior Year, or Budget?

Thanks,

Alex.
Jul 20 '05 #1
  • viewed: 8216
Share:
3 Replies
Alex,

Put WHERE clause after FROM. You can check "Books Online" for the complete
syntax.

Shervin

"Alex" <al**@totallynerd.com> wrote in message
news:2b**************************@posting.google.c om...
Hi folks,

Hopefully this is a simple fix, but I keep getting Syntax error with
this statement in an MS SQL DTS statement and in Query Analyzer:

Update A Set A.deptcode = A.deptcode,
A.type = A.Type,
a.TotalExpenseUnit = (a.LaborExpenseUnit + a.OtherExpenseUnit)
Where a.Type in ('FYTD04', 'Prior Year','Budget')
From Data_Unsorted A Join Data_Unsorted B On
A.deptcode = B.deptcode and A.type = B.Type

Below is the error from Query Analyzer:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'From'.

Where do I place my Where..In statement since I only want to limit the
Update to run for items where a.type is FYTD04, Prior Year, or Budget?

Thanks,

Alex.

Jul 20 '05 #2
Alex (al**@totallynerd.com) writes:
Hopefully this is a simple fix, but I keep getting Syntax error with
this statement in an MS SQL DTS statement and in Query Analyzer:

Update A Set A.deptcode = A.deptcode,
A.type = A.Type,
a.TotalExpenseUnit = (a.LaborExpenseUnit + a.OtherExpenseUnit)
Where a.Type in ('FYTD04', 'Prior Year','Budget')
From Data_Unsorted A Join Data_Unsorted B On
A.deptcode = B.deptcode and A.type = B.Type

Below is the error from Query Analyzer:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'From'.

Where do I place my Where..In statement since I only want to limit the
Update to run for items where a.type is FYTD04, Prior Year, or Budget?


WHERE always comes after the FROM clause.

There is a syntax diagram for UPDATE in Books Online.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
You might want to write real SQL instead of a dialect. There is no
FROM clause in Standard SQL and the products that allow the bastard
syntax have different semantics -- it is non-portable, non-standard
and dangerous!

UPDATE A
SET total_expense_unit
= COALESCE((SELECT D1.laborexpenseunit + D1.otherexpenseunit
FROM Data_Unsorted AS D1
WHERE D1.type IN ('FYTD04', 'prior year', 'budget')
AND A.deptcode = D1.deptcode
AND A.type = D1.type),
total_expense_unit);

I am confused by the name "Data_Unsorted", since tables have no
ordering. This is written with a COALESCE() in case the
"Data_Unsorted" table does not have a match to the "A" table.

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by SQL_developer | last post: by
121 posts views Thread by typingcat | last post: by
reply views Thread by Metal2You | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.