Table-driven deletes and updates

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.

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top