Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

SQL Update query producing syntax error

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
wbstech
Asked:
wbstech
1 Solution
 
wbstechAuthor Commented:
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
 
mnrzCommented:
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
 
wbstechAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what database are you using?
the syntax to update 1 table from another varies alot.
0
 
wbstechAuthor Commented:
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
 
wbstechAuthor Commented:
Sorry, forgot to mention. I was doing it in SQL Enterprise manager on MS SQL Server (2000)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
LowfatspreadCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now