CODEWITHSUNDEEP
sqlteradatateradata-sql-assistant
ferye21
ferye21

Reputation: 23

Delete statement are working but doesn't delete rows

I want to make a delete statement on which I want to delete some articles that are in another selected table. I created the statement but when I run it, it doesn't delete something. It's running but no rows deleted.

delete from article  where (client_id, art_no) in ( select art_no, client_id from art_del as A 
inner join (select distinct client_id from article) as D on a.cliend_id = d.client_id 
where label not in (0,-1));

The data in the where clause looks good, but when I execute with the delete it won't delete something.

Upvotes: 1

Views: 237

Answers (1)

forpas
forpas

Reputation: 164089

The where clause is this:

where (client_id, art_no)

so the pair has 1st client_id and then art_no but in the subquery the order is differenet:

select art_no, client_id

Change to:

delete from article  
where (client_id, art_no) in ( 
  select client_id, art_no
  from art_del as A inner join (
    select distinct client_id 
    from article
  ) as D on a.cliend_id = d.client_id 
  where label not in (0,-1)
);

Upvotes: 2

Related Questions