How can I delete rows in a table1 using table2 when table1.ID=table2.ID with one statement? How can I update the fields in table1 with table2? The structure is table2:ID,fieldnumber,value.
Assuming the one statement permits a subquery, it would be --
delete from table1 where exists ( select * from table2 where ID = table1.ID )
One database I know of where this should not work is MySQL which does not support subqueries.
Some databases have extensions to standard SQL. For example, I believe Microsoft SQL/Server will let you say --
delete from table1 from table1, table2 where table1.ID = table2.ID
(Yes, there are two "from"s in there; the first one's optional.)
As for table-driven updates, I would write --
update table1 set fieldnumber = (select fieldnumber from table2 where ID = table1.ID) , fieldvalue = (select fieldvalue from table2 where ID = table1.ID)
I say "would" because I've always done it the SQL/Server way --
update table1 set table1.fieldnumber = table2.fieldnumber , table1.fieldvalue = table2.fieldvalue from table1, table2 where table1.ID = table2.ID
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.