CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ananth
Ananth

Reputation: 10730

Why doesn't this simple subquery work?

I am trying to write a simple correlated sub query that deletes all records for the selected members except the latest one.

DELETE FROM table1  p
WHERE member_id IN (1, 2,3)
AND create_dttm < (SELECT MAX(create_dttm) 
                   FROM table1   
                   WHERE  member_id = p.member_id)

But I get an error message

Incorrect syntax near 'p'.

I can easily write 3 queries to get the work done. But was curious to know what is going wrong here ? Could you please enlighten me how to write this query correctly ?

Thanks in advance

Upvotes: 2

Views: 160

Answers (4)

Andriy M
Andriy M

Reputation: 77737

As an alternative, you could alias the table in the subquery and reference the outer instance's member_id by the table's real name:

DELETE FROM table1
WHERE WHERE member_id IN (1, 2,3)
AND create_dttm < (SELECT MAX(create_dttm) 
                   FROM table1 p
                   WHERE  table1.member_id = p.member_id)

In SQL Server, when you assign an alias to a table, you can no longer reference that table by its original name in the same statement. So, prefixing columns with table1. in the subquery, you would certainly be referencing the outer table.

Upvotes: 2

chamara
chamara

Reputation: 12709

Try following

DELETE p FROM table1 p
WHERE member_id IN (1, 2,3)
AND create_dttm < (SELECT MAX(create_dttm) 
                   FROM table1   
                   WHERE  member_id = p.member_id)

Upvotes: 1

Tobi
Tobi

Reputation: 1438

And use some Alias for the tables:

DELETE FROM table1 p
WHERE p.member_id IN (1, 2,3)
AND p.create_dttm < (SELECT MAX(p1.create_dttm) 
                   FROM table1 p1 
                   WHERE p1.member_id = p.member_id)

Upvotes: 1

John Dewey
John Dewey

Reputation: 7093

Try

DELETE p
FROM table1  p
WHERE member_id IN (1, 2,3)
AND create_dttm < (SELECT MAX(create_dttm) 
                   FROM table1   
                   WHERE  member_id = p.member_id)

Upvotes: 2

Related Questions