SQL Update Help

[warrenk]

I am trying to update a table based on a join with another table...

update supplement
set supplement.supp_desc = table2.description, supplement.supp_ingred = table2.ingredients, supplement.supp_directions = table2.direction
(select * FROM supplement, table2 where supplement.brand_id = table2.brandid and
supplement.supp_name = table2.item and supplement.supp_size = table2.size)

Can anyone help me with the proper syntax? I keep getting errors. Thanks for any help! - Warren

 

[r937]

correct syntax will depend on which database system you're using

access? db2? mysql?

r937.com | rudy.ca

 

[fredericofonseca]

in some of the DB's (which you failed to mention which one you are using), it is possible to do

update tbl
set (field1, field2, field3) = (select other_field1, other_field2, other_field3 from my_other_tables where other_conditions)
where tbl.fieldx = y and tbl.fieldy = z


Also its never advisable to use the "select * from" as it may lead to headaches in the future.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com