Delete records with various conditions

Navigation
-1

I need to exclude duplicate records in column bidding_price with the following conditions:

Table: bid_account

Columns to check:

  

id = PRIMARY KEY
auction_id = ID of each product
bidding_price = value entered (the one that should be checked   duplicity for each product)
bid_flag = should always be the same   to the value of: 'd'
bidding_type = should always be equal to   value of: 's'

There will always be equal records in the bidding_price column, which can not have equal records with the same product ID ( auction_id ).

Example of how it should not have:

  

auction_id | bidding_price
------ 10 ------------ 0.02
  ------ 10 ------------ 0.02   ------ 11 ------------ 0.02   ------ 11 ------------ 0.02

The correct one would be:

  

auction_id | bidding_price
------ 10 ------------ 0.02
  ------ 11 ------------ 0.02

I tried with the following command: 

DELETE ba
    FROM bid_account ba JOIN
         (SELECT ba2.auction_id, ba2.bidding_price, MAX(ba2.id) as max_id
          FROM bid_account ba2
          WHERE ba2.bid_flag = 'd' AND ba2.bidding_type = 's'
          GROUP BY ba2.auction_id, ba2.bidding_price
         ) ba2
         ON ba2.auction_id = ba.auction_id AND
            ba2.bidding_price = ba.bidding_price AND
            ba2.max_id < ba.id
WHERE ba.bid_flag = 'd' AND ba.bidding_type = 's' AND ba.auction_id = ba2.auction_id

Except he deleted multiple records, he did not do the validations correctly. How can I do it?

    
asked by anonymous 16.04.2018 / 03:30

1 answer

0
DELETE ba1 FROM bid_account ba1, bid_account ba2, 
         WHERE ba1.auction_id = ba2.auction_id AND
             ba1.bidding_price = ba2.bidding_price AND
             ba1.max_id < ba2.id AND
                 ba1.bid_flag = 'd' AND 
                 ba1.bidding_type = 's';
    
16.04.2018 / 18:56
checkbox in datagridview How to set Jenkins with 24-hour format