wbstech
asked on
SQL Update query producing syntax error
I have the below query:
UPDATE insurance_details_tbl, order_details_tbl
SET insurance_details_tbl.orde r_id = order_details_tbl.order_id
WHERE order_details_tbl.order_id = insurance_details_tbl.orde r_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.
UPDATE insurance_details_tbl, order_details_tbl
SET insurance_details_tbl.orde
WHERE order_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.
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)
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)
ASKER
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""
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""
what database are you using?
the syntax to update 1 table from another varies alot.
the syntax to update 1 table from another varies alot.
ASKER
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.stud ent_id = order_details_tbl.student_ id;
UPDATE insurance_details_tbl
SET order_id = order_details_tbl.order_id
FROM order_details_tbl
WHERE insurance_details_tbl.stud
ASKER
Sorry, forgot to mention. I was doing it in SQL Enterprise manager on MS SQL Server (2000)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
well, AFAIK, SQL Server does not require the keyword INNER for inner join, only MS Access requires it...
ASKER
UPDATE insurance_details_tbl, order_details_tbl
SET insurance_details_tbl.orde
WHERE order_details_tbl.student_