Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

deleting more than one rows sql

 
vinoth Robert
Ranch Hand
Posts: 31
Firefox Browser MS IE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Campbell Ritchie
Sheriff
Posts: 51332
86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 20744
68
Chrome Eclipse IDE Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 20744
68
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SQL Server 2005 certainly does not support that syntax.
 
vinoth Robert
Ranch Hand
Posts: 31
Firefox Browser MS IE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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!
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic