[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Update query producing syntax error

Posted on 2007-03-19
11
Medium Priority
?
198 Views
Last Modified: 2010-08-05
I have the below query:

UPDATE    insurance_details_tbl, order_details_tbl
SET              insurance_details_tbl.order_id = order_details_tbl.order_id
WHERE     order_details_tbl.order_id = insurance_details_tbl.order_id;

Should be quite obvious what i'm attempting to do. But I am getting "Syntax Error Line 1 produced near ',' ".

Could someone provide me with the correct syntax. Thanks.
0
Comment
Question by:wbstech
[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
11 Comments
 
LVL 4

Author Comment

by:wbstech
ID: 18747026
Woops - the above query should be:
UPDATE    insurance_details_tbl, order_details_tbl
SET              insurance_details_tbl.order_id = order_details_tbl.order_id
WHERE     order_details_tbl.student_id = insurance_details_tbl.student_id;
0
 
LVL 8

Expert Comment

by:mnrz
ID: 18747030
you cant place two table name:

try this:

UPDATE    insurance_details_tbl as t1
SET              order_id = (select order_id  from order_details_tbl as t2 where
t1.order_id = t2.order_id)
0
 
LVL 4

Author Comment

by:wbstech
ID: 18747051
I just did
UPDATE    insurance_details_tbl AS t1
SET              order_id =
                          (SELECT     order_id
                            FROM          order_details_tbl AS t2
                            WHERE      t1.student_id = t2.student_id)

(Modified your query to fix my mistake)

This produced "Incorrect syntax near "As""
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18747068
what database are you using?
the syntax to update 1 table from another varies alot.
0
 
LVL 4

Author Comment

by:wbstech
ID: 18747083
Just did this and it worked:

UPDATE    insurance_details_tbl
SET              order_id = order_details_tbl.order_id
FROM         order_details_tbl
WHERE     insurance_details_tbl.student_id = order_details_tbl.student_id;
0
 
LVL 4

Author Comment

by:wbstech
ID: 18747088
Sorry, forgot to mention. I was doing it in SQL Enterprise manager on MS SQL Server (2000)
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 18747108
please try this syntax, then:

UPDATE    insurance_details_tbl
SET              order_id = od.order_id
FROM         insurance_details_tbl id
JOIN          order_details_tbl od
WHERE     id.student_id = od.student_id
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 18747646
correction to angeliii don't give me points

UPDATE    insurance_details_tbl
SET              order_id = od.order_id
FROM         insurance_details_tbl id
Inner JOIN          order_details_tbl od
on      id.student_id = od.student_id
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18747683
well, AFAIK, SQL Server does not require the keyword INNER for inner join, only MS Access requires it...
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

650 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