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. 3 8398
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.
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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: PG |
last post by:
When deleting a row from the database, that id is now missing.
So what I'm trying to do is update in a loop (maybe an sql loop if
there is one) of all of the id numbers - sort of like renaming...
|
by: SQL_developer |
last post by:
Hello,
I am trying to update a column in a table with an 8 digit string.
The 8 digit number is obtained from another column which is a comments
field.
e.g.
Comments Field :
|
by: chriskoch |
last post by:
I am running a DTS Package.
I have a temp table with 1494 records. I am inserting a 'Y' or
'N'into a temp table #HasClaims.
The TempTable name with the Provider Id's(PRPR_ID) is...
|
by: typingcat |
last post by:
First of all, I'm an Asian and I need to input Japanese, Korean and so
on. I've tried many PHP IDEs today, but almost non of them supported
Unicode (UTF-8) file.
I've found that the only Unicode...
|
by: Metal2You |
last post by:
I'm working on an ASP.NET 2.0 application in Visual Studio 2005 that
accesses a Sybase database back end. We're using Sybase SQL Anywhere
9.0.2.3228. I have installed and registered the Sybase...
|
by: Ian Davies |
last post by:
Hello
Needing help with a suitable solution.
I have extracted records into a table under three columns 'category',
'comment' and share (the category column also holds the index no of the
record...
|
by: KevinADC |
last post by:
Note: You may skip to the end of the article if all you want is the perl code.
Introduction
Uploading files from a local computer to a remote web server has many useful purposes, the most...
|
by: shivendravikramsingh |
last post by:
hi friends, i m using a ajax function for retrieving some values from a database table,and display the values in required field,my prob is that the ajax function i m using is working f9 once,but if...
|
by: javediq143 |
last post by:
Hi All,
This is my first post in this forum. I'm developing a CMS for my latest website. This CMS is also in PhP & MySQL. I'm done with the ADD section where the Admin can INSERT new records in...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |