This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan 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 Android Security Essentials Live Lessons this week in the Android 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: 38040
    
  22
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: 19654
    
  18

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: 3606
    
  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: 19654
    
  18

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!
 
Consider Paul's rocket mass heater.
 
subject: deleting more than one rows sql
 
Similar Threads
Uni JAVA Exam Monday HELP!!
How to store data in an application which will be distributed
printing multiple pages
Design question - update + insert versus delete + insert
DB operations and java.lang.RuntimeException: No columns in table: X