Search code examples
sqlsql-servert-sqlsql-delete

When column = ... then delete

I'm trying to create a query where a row gets deleted from a table if it meets the condition.

Pseudo code: If indexi = 500 and user = 290045 then delete from table1 where indexi = 85 and user = 290045

user   | line_no | header | indexi |
-------+---------+--------+--------+
290045 |    0    |   0    |   500  |
290045 |    1    |   0    |    85  |
733    |    0    |   0    |    33  |

Expected result:

user   | line_no | header | indexi |
-------+---------+--------+--------+
290045 |    0    |   0    |   500  |
733    |    0    |   0    |   33   |
Solution

  • I think you want exists:

    delete t
from mytable t
where 
    t.indexi = 85 
    and t.usr = 290045
    and exists (select 1 from mytable t1 where t1.usr = t.usr and t1.indexi = 500)

    Side note: usr is a reserved word in SQL Server, hence not a good choice for a column name.