File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
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 EJB 3 in Action this week in the EJB and other Java EE Technologies 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: 36453
    
  15
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: 19541
    
  16

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: 3433
    
  47

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: 19541
    
  16

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!
 
I agree. Here's the link: http://aspose.com/file-tools
 
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
DB operations and java.lang.RuntimeException: No columns in table: X
Design question - update + insert versus delete + insert