By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,505 Members | 1,165 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,505 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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.