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

Alex

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

Shervin Shapourian

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

Erland Sommarskog

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

--CELKO--

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