Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Deleting records in one table based on data in another table - SQL

 
Mansukhdeep Thind
Ranch Hand
Posts: 1158
Eclipse IDE Firefox Browser Java
  • Mark post as helpful
  • send pies
  • 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
 
Dave Tolls
Ranch Hand
Posts: 2102
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Any particular database?
Are you getting an error?
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • 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.
 
K. Tsang
Bartender
Posts: 3583
16
Android Java
  • Mark post as helpful
  • send pies
  • 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
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic