0

I have a task at hand to identify negative values in a table and find a matching positive value in same table/column, then delete both entries. It is possible that there could be multiple positive values in the column and in such case only one of the matching positive values along with the negative value should be deleted. There will only be one negative value but could be multiple matching positive values. Values in this case are numerical (transaction data).

As an example, table 'Transactions' and column 'Value' has: -10.02, 10.02, 10.02

I require a sql query that will delete the negative value and one of the matching positive values.

I have tried using 'select abs' to convert negative to positive and then 'having count' but it only returns the 2 positive values. I am likely on the wrong track and have very basic SQL skills.

All help will be appreciated!

Tryinghard
  • 1
  • 1
  • 2

1 Answers1

0

Your problem description makes it unclear if it is possible that for a given negative value no matching positive value exists, and what to do in that case.

If it is guaranteed that there will be at least one positive value for every negative value, go with this (assuming you have a column 'id'):

DELETE FROM transactions WHERE id IN (
    SELECT tid FROM (
        SELECT id AS tid FROM transactions WHERE value IN (
            SELECT (value * ( -1 )) FROM transactions WHERE value <0
        )
        GROUP BY value
    ) AS tmp
);
DELETE FROM transactions WHERE value < 0;

The GROUP BY guarantees that exactly one ID will occur, but not a certain ordering. We need the SELECT tid FROM ... AS tmp part due to the limitation of MySQL to modify a table that is directly used in a SELECT part, well documented here: MySQL Error 1093 - Can't specify target table for update in FROM clause

If you are not using MySQL, there are several more elegant variants that could be used, but the code above should work for most SQL products.

Community
  • 1
  • 1
Sliverfish
  • 26
  • 2
  • Thanks for the quick response. I correct myself and will be more specific in my query. All entries (negative and positive numbers) are within one column only in one table. If there are no matching positive values to the negatives then IGNORE the negative entries, as the downstream system will reject the processing of the negative numbers (which i am ok with). I am using SQL Server 2008 R2. I am not sure about 'column id', the example specified, as in table name: transactions' and column header: 'value' holds true. – Tryinghard Aug 06 '15 at 21:11