I am writing a SQL query to delete the data from one table-CodePudding

I am writing a SQL query where I have one table called records and in the records table we have two columns Security and Position. I want to write a query to fulfill below two queries

When the same security have both long and short available then we need to remove all the long and load only short.
When the same security have only the long position and no short position available for that then we don't want to delete any long we need to load all the long position of the same security.

Expected result :

For point 1, I have create a SQL query and it's working fine, but for point 2 I did not find the way can someone please help me.

This is the SQL which I wrote:

DELETE a
FROM Records a 
INNER JOIN 
    (SELECT [Security]
     FROM Records
     GROUP BY [Security]
     HAVING COUNT(*) > 1) b ON a.[Security] = b.[Security]
                            AND a.position = 'Long';

CodePudding user response：

with cte as (
    select Security, count(distinct(Position)) as cnt
    from records
    group by Security
)
delete from records 
where Position = 'Long'
and Security = (select Security from cte where cnt = 2);

For reference check this: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e20f5769b10150663cc13a2e79cb9d53

CodePudding user response：

We can try finding all securities having both long and short positions, in a CTE. Then, delete all securities which have both types of positions and also where the position is long.

WITH cte AS (
    SELECT Security
    FROM Records
    WHERE Position IN ('Short', 'Long')
    GROUP BY Security
    HAVING MIN(Position) <> MAX(Position)
)

DELETE
FROM Records
WHERE Position = 'Long' AND
      Security IN (SELECT Security FROM cte);

CodePudding user response：

You want to delete all 'Long' rows where a 'Short' row for the same security exists. Use EXISTS or IN for this.

delete from records
where position = 'Long'
and security in (select security from records where position = 'Short');

CodePudding user response：

You could delete the records using a CTE as below. Instead of hardcoding the positions in the where predicate, the row number could be calculated by sorting the position in descending order.

declare @tbl table (security varchar(40),position varchar(20))

insert into @tbl
values
('Sahil','long'),
('Sahil','long'),
('Sahil','short'),
('John','short'),
('Mark','long'),
('Mat','long'),
('Mat','long')

;with cte as(
select *,ROW_NUMBER()over(partition by security order by position desc) rownum from(
select * from
@tbl
group by security,position)t
)
delete t from 
@tbl t
inner join cte c
on t.security = c.security
and t.position = c.position
where c.rownum = 2

select * from @tbl