Solved

SQL Update query producing syntax error

Posted on 2007-03-19
11
193 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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 500 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

740 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