How can I delete rows in a table1 using table2 when table1.ID=table2.ID with one statement? How can I update the...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.