• 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 records in one table based on data in another table - SQL

 
Ranch Hand
Posts: 1164
Eclipse IDE Firefox Browser Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi all

I have a use case wherein I want to do something on the line of -

delete from table1 inner join table2 ON table1.column1 = table2.column2 where table1.col2 = ? and table1.col3 = ?, table2.column3 IN (?,?,?,?....) ;

I want to use this in a PreparedStatement in Java code with multiple AND conditions. I tried quite a few things but nothing seems to work. This is a link that followed : http://www.databasically.com/2011/08/31/using-sql-to-delete-rows-from-a-table-using-inner-join/

Any guidance would be appreciated!

~Mansukh
 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Any particular database?
Are you getting an error?
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Mansukhdeep Thind wrote:


I''m not sure, but depending on your database you might be able to use a nested query for this e.g.

I don't use JDBC myself, so you'll need to figure out how to pass in the list of values for comparing with table2.column3, but that's a separate issue.

You don't want to execute a DELETE until you're sure you are removing the right records, so I suggest you get the nested query working first, so you can be sure it's identifying the right records in table2. Then check the subquery works when querying from table1, by doing SELECT * FROM table1 in the above SQL instead of DELETE FROM table1. If this query returns the records from table1 that you want to delete, then you can go ahead and implement the DELETE statement.

Use your database's SQL shell to do this, as it will be much easier.
 
Bartender
Posts: 3648
16
Android Mac OS X Firefox Browser Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Further to Chris's post, it may be better to get the primary keys of those rows from the subquery join sql and use that in the delete statement.

This way, you wouldn't need to do the subquery join thing again.
 
chris webster
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

K. Tsang wrote:Further to Chris's post, it may be better to get the primary keys of those rows from the subquery join sql and use that in the delete statement.

This way, you wouldn't need to do the subquery join thing again.


Good point.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic