?
Solved

SQL Update query producing syntax error

Posted on 2007-03-19
11
Medium Priority
?
197 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
Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

762 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