Solved

SQL Update query producing syntax error

Posted on 2007-03-19
11
190 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
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 142

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 142

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 142

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

832 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