This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes JDBC and the fly likes deleting more than one rows sql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "deleting more than one rows sql" Watch "deleting more than one rows sql" New topic
Author

deleting more than one rows sql

vinoth Robert
Ranch Hand

Joined: Feb 02, 2011
Posts: 31

hi,
this is nearly sql question,

i'm having a table X containing 12 columns and 10 rows, in this combination of first six columns is primary key.
i have 5 rows in an array which exactly matches with the rows of table X.

now i want to delete the rows which are not matched with that 5 rows.

without deleting all rows and insert those 5 rows

any idea?


vinoth.R
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 39478
    
  28
Welcome to the Ranch

Can you pull out the primary keys from the 5 wanted rows, put them into an enumeration, and then DELETE . . . NOT IN . . . ?
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19723
    
  20

That should definitely work with a single column primary key, but six (heck, even two) makes it a bit more difficult. That's because the IN operator needs a single value on the left hand side, not six.

An alternative (not a nice one, but OK) is to use a lot of ANDS:

I'll kick this thread over to our JDBC forum.


One more hint: before you ever try such a DELETE statement without knowing if it's going to work, turn that statement into a SELECT statement and see what it returns. To do this, simply replace the word DELETE with "SELECT * FROM". The same warnings goes out for UPDATE statements. Unless you're certain what's going to be deleted / updated, first check.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Rob Spoor wrote:That should definitely work with a single column primary key, but six (heck, even two) makes it a bit more difficult. That's because the IN operator needs a single value on the left hand side, not six.

That depends on the database. Oracle, for example, supports specifying multiple values in IN clauses, as inI've actually thought this is part of SQL standard, though I'm not at all sure.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19723
    
  20

SQL Server 2005 certainly does not support that syntax.
vinoth Robert
Ranch Hand

Joined: Feb 02, 2011
Posts: 31

Rob Spoor wrote:That should definitely work with a single column primary key, but six (heck, even two) makes it a bit more difficult. That's because the IN operator needs a single value on the left hand side, not six.

An alternative (not a nice one, but OK) is to use a lot of ANDS:

I'll kick this thread over to our JDBC forum.


One more hint: before you ever try such a DELETE statement without knowing if it's going to work, turn that statement into a SELECT statement and see what it returns. To do this, simply replace the word DELETE with "SELECT * FROM". The same warnings goes out for UPDATE statements. Unless you're certain what's going to be deleted / updated, first check.


thanks rob!
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: deleting more than one rows sql