• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

deleting more than one rows sql

 
Ranch Hand
Posts: 32
Hibernate Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Marshal
Posts: 79177
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 . . . ?
 
Sheriff
Posts: 22783
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 22783
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
SQL Server 2005 certainly does not support that syntax.
 
vinoth Robert
Ranch Hand
Posts: 32
Hibernate Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!
 
I didn't say it. I'm just telling you what this tiny ad said.
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic