Win a copy of Testing JavaScript Applications this week in the HTML Pages with CSS and JavaScript forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

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
  • 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: 4625
47
  • Mark post as helpful
  • send pies
  • 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
  • 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
Mac OS X Firefox Browser 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
36
Scala Python Oracle Postgres Database Linux
  • 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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
    Bookmark Topic Watch Topic
  • New Topic