Complex Delete SQL Statement

Question

Why do delete statements have limitations beyond their select counterparts?

I am not stuck because the problem was easy enough to work around but I'd rather fix my understanding than continue using workarounds.

Case in point, I have an undirected, edge list with fields, V1 and V2. Unfortunately, when creating the table, I introduced duplicates (i.e. V1 -> V2 and V2 -> V1). To identify the dups, I ran the query:

SELECT t1.V1, t1.V2
FROM table t1
WHERE t1.V1 > t1.V2
    and EXISTS (
        SELECT *
        FROM table t2
        WHERE t2.V1 = t1.V2
          and t2.V2 = t1.V1 )

since this returned a nice set of rows from the table, I thought I should be able to replace the select line with delete and rerun the same query. Sql server however, got mad and gave me red letters instead. Once about syntax and after a tweak, something about binding multipart identifiers.

I managed to store the select in a table variable and get the delete done - which is ok, but what was wrong with my original approach and could I have done it with a single SQL statement?

It might help if you reported exactly what the "red letters" said. And, also exactly what your DELETE statement looked like (was it "DELETE t1" followed by the FROM...?). — GilM
– GilM, Commented Apr 29, 2013 at 18:06
Your table is actually called table ? Would you name your children child ? — wildplasser
– wildplasser, Commented Apr 29, 2013 at 18:20

Andomar · Accepted Answer · 2013-04-29 18:01:49Z

4

SQL Server is fairly flexible with delete. The double from syntax is allowed pretty much wherever select is:

DELETE FROM t1
FROM table t1
WHERE t1.V1 > t1.V2
    and EXISTS (
        SELECT *
        FROM table t2
        WHERE t2.V1 = t1.V2
          and t2.V2 = t1.V1 )

answered Apr 29, 2013 at 18:01

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bob Over a year ago

That's cool! Did not know about the double from. Have to look that up. Thanks!

Dan Bracuk · Accepted Answer · 2013-04-29 17:55:51Z

1

I think you'll need syntax resembling this;

delete from yourTable
where v1 in 
(select v1
 from etc
)

answered Apr 29, 2013 at 17:55

Dan Bracuk

20.8k5 gold badges29 silver badges44 bronze badges

1 Comment

bob Over a year ago

maybe but both conditions to identify the duplicate edge so the where clause in the subquery has to account for etc.1 = yourTable.2 and etc.2 = yourTable.1.

Collectives™ on Stack Overflow

Complex Delete SQL Statement

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related