Q

Table-driven deletes and updates

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

This was first published in July 2001

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close