Solved

ms sql  - incorrect syntax issue with update statement

Posted on 2010-09-20
9
229 Views
Last Modified: 2012-05-10
getting an error with my update statement:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'dd'.



update ss_date_example dd
join soda ss on dd.soda_id = ss.soda_id
join #temp tt on ss.juice_number = tt.juice_number
set dd.case_account_id = tt.case_desired_account
where tt.milk_id = dd.milk_id
and tt.juice_number = ss.juice_number
0
Comment
Question by:GlobaLevel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 33719125
update dd
set dd.case_account_id = tt.case_desired_account
from ss_date_example dd
join soda ss on dd.soda_id = ss.soda_id
join #temp tt on ss.juice_number = tt.juice_number
where tt.milk_id = dd.milk_id
and tt.juice_number = ss.juice_number
0
 
LVL 41

Accepted Solution

by:
ralmada earned 250 total points
ID: 33719136
and actually you don't need the last filter, since you're already doing that in the join
update dd
set dd.case_account_id = tt.case_desired_account
from ss_date_example dd
join soda ss on dd.soda_id = ss.soda_id
join #temp tt on ss.juice_number = tt.juice_number
where tt.milk_id = dd.milk_id
 
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33719406
the data doesnt change though correct?  does it change the data if I put it in the join or the where clause?
0
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
LVL 41

Expert Comment

by:ralmada
ID: 33719562
the result is the same, just that filter was redundant.
0
 
LVL 70

Assisted Solution

by:Qlemo
Qlemo earned 250 total points
ID: 33720742
What ralmada tried to say: The join for the update is already giving you only records which are found in both  tables. Thus the additional restriction in the where clause is superfluous.

MSSQL does not allow to provide a join directly in the UPDATE clause. You can either do
update tbl1set ...from tbl2where tbl1.x = tbl2.x
or
update alias1set ...from tbl1 alias1 join tbl2 alias2 on ....
If you have something more complex to update, it is better to first create a (updatable) select for checking the results,
select dd.case_account_id, tt.case_desired_accountfrom ss_date_example dd join soda ss on dd.soda_id = ss.soda_idjoin #temp tt on ss.juice_number = tt.juice_number     and  tt.milk_id = dd.milk_id
and then use the select for update:
update x
set case_account_id = case_desired_account
from
(select dd.case_account_id, tt.case_desired_account
 from ss_date_example dd
 join soda ss on dd.soda_id = ss.soda_id
 join #temp tt on ss.juice_number = tt.juice_number
      and  tt.milk_id = dd.milk_id) x
That way you can be certain you do not update anything unwanted.
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33720955
Can o run this update thru Asp.net to a ms SQL db 2005??? Thanks all
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33720957
Can o run this update thru Asp.net to a ms SQL db 2005??? Thanks all
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33722145
certainly you can.
0
 
LVL 10

Author Closing Comment

by:GlobaLevel
ID: 33780257
thks
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question