0

At present the DB look like this:

item club
2    10
2    20
2    30
3    30
3    40
3    60
4    30
4    40
4    60

I want to delete club 10 of item 2 and club (30,40) of item 3.

For that I am using this query

DELETE from allocation.testDelete where item in(2,3) and club in(10,30,40)

Now I know what wrong I am doing. I can delete first clubs for item 2 and then clubs for item 3 but can't we do it in a single query? I need to do in one transaction if possible. I am using JPA native for it. Which looks like delete from testDelete a where a.itemNbr in (:itemNbrs) and a.clubNbr in (:clubNbrs)

PAMPA ROY
  • 25
  • 5
  • 1
    `where (item = 2 and club = 10) or (item = 3 and ...)` – jarlh Aug 21 '23 at 12:47
  • Yes, but you need to use an `AND` and `OR`. Or, alternatively, you can `JOIN` to a dataset that contains the rows you want to `DELETE`, such as a table variable or `VALUES` table construct. – Thom A Aug 21 '23 at 12:47
  • Obligatory note that SQL Server 2008 ran *completely* out of support **4 years** ago. It is *long* past time you implemented your upgrade path by now. – Thom A Aug 21 '23 at 12:48
  • BTW, a transaction can include several DELETE's. – jarlh Aug 21 '23 at 12:48
  • If JPA doesn't let you make clauses any more complex than that, either stop using JPA, or issue two different deletes (assuming JPA also lets you wrap statements in a transaction ... I already get the sense it is very limited). – Stuck at 1337 Aug 21 '23 at 12:48
  • 1
    I'm *really* confused that the most upvoted answer on that duplicate uses syntax that isn't supported by SQL Server... Sure, it states SQL Server doesn't support the syntax, but that's like giving a solution in Java to a question for C#... – Thom A Aug 21 '23 at 12:58
  • Use IN tuples. The linked question explains how. – O. Jones Aug 21 '23 at 12:58
  • @O.Jones The linked question explains how the standard defines it, but unfortunately that syntax hasn't been implemented in SQL Server. – Stuck at 1337 Aug 21 '23 at 13:00
  • 1
    @ThomA I sure wish I could get 84 upvotes for saying "this is how SQL Server should have done it." – Stuck at 1337 Aug 21 '23 at 13:00
  • The "2nd" [answer](https://stackoverflow.com/a/8006998/2029983) has syntax supported in SQL Server at least, @Stuckat1337 . – Thom A Aug 21 '23 at 13:01
  • 1
    Maybe I should start finding MySQL questions and posting "This is how MySQL *should* work" answers and watch the reputation come flooding in, @Stuckat1337 . – Thom A Aug 21 '23 at 13:02

0 Answers0