Incorrect Syntax in Update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Incorrect Syntax in Update

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2010-10-12 : 15:33:23

Working with two tables.

customer


cus_no     uf1
  908
  903
  901
  910

order1


cus_no       inv_dt     tot_sls_amt
   908      10/3/2010      1000.00
   908      10/4/2010       500.00
   903      10/14/2010      300.00
   901      10/20/2010      400.00
   901      10/23/2010      400.00
   901      10/13/2010      400.00

What I'm trying to accomplish is adding the tot_sls_amt and updating uf1 in the customer table.


customer
cus_no     uf1
  908     1500.00
  903      300.00
  901     1200.00
  910        0.00

Here is what I've tried. Get incorrect syntax near keyword where


update customer
set customer.uf1 = (select tot_sls_amt1
from(select cus_no,sum(tot_sls_amt)
as total_sls_amt1 from order1
group by cus_no)
where order1.cus_no = customer.cus_no and month(order1.inv_dt) = month(getdate()) and year(order1.inv_dt)=year(getdate()))
from order1 join customer on order1.cus_no = customer.cus_no

mfemenel
Professor Frink

1421 Posts

Posted - 2010-10-12 : 17:15:06

you need to give your sub select an alias name:
update customer
set customer.uf1 = (select tot_sls_amt1
from(select cus_no,sum(tot_sls_amt)
as total_sls_amt1 from order1
group by cus_no)base
where order1.cus_no = customer.cus_no and month(order1.inv_dt) = month(getdate()) and year(order1.inv_dt)=year(getdate()))
from order1 join customer on order1.cus_no = customer.cus_no

Mike
"oh, that monkey is going to pay"

Kristen
Test

22859 Posts

Posted - 2010-10-13 : 04:01:32

Needs more fixes than that.

tot_sls_amt1 and total_sls_amt1 names do not correlate.

ORDER1 table is referenced outside the inner select. I expect that will correlate with the outer ORDER1, and will work, but probably not give you what you want.

Why is ORDER1 joined to CUSTOMER in the outer select when only CUSTOMER is involved in the update?

You need to describe the action you want to take place.

Do you only want the [total_sls_amt1] total to include orders within the current month? or do you want it to total of ALL orders but to only update the customers who have orders in the current month? (which appears to be nearer to what you query is written to achieve)

Why is ORDER1 table referenced in the outer query? Is this just for customers who have orders? or are you also trying to also do something with the Order1 table?

And don't use MONTH() and YEAR() in the WHERE clause, it will not be optimised (e.g. if you have an index on order1.inv_dt), use a date range instead, eg.


AND inv_dt >= DATEADD(Month, DATEDIFF(Month, 0, getdate()), 0)
AND inv_dt <  DATEADD(Month, DATEDIFF(Month, 0, getdate())+1, 0)

and format the code!!!!!!!, I have no idea how you can see what is going on in your query - I can't

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources