CODEWITHSUNDEEP
mysqlsql-server
Mariusz Sołtys
Mariusz Sołtys

Reputation: 31

Unknown column in WHERE clause in subquery

I have pages table which I'm trying to truncate. I need to leave last 10 versions of each page (page is identified by uid).

id----|----uid----|----version

1 ----|---- 1 ----|---- 1

2 ----|---- 1 ----|---- 2

3 ----|---- 1 ----|---- 3

4 ----|---- 1 ----|---- 4

5 ----|---- 2 ----|---- 1

6 ----|---- 2 ----|---- 2

...........

55 ----|---- 1 ----|---- 23

56 ----|---- 2 ----|---- 14

57 ----|---- 2 ----|---- 15

I've tried with this MySQL query:

DELETE FROM pages AS p WHERE p.id IN (
    SELECT Versions.id FROM
        (SELECT q.id FROM pages AS q WHERE p.uid = q.uid ORDER BY q.version DESC LIMIT 10) 
    AS Versions
)

but above SQL returns:

Unknown column 'p.uid' in 'where clause'

On Stackoverflow is loads of answers for similar questions like mine but as I'm not SQL ninja I wasn't able to convert any of these to fit my problem. I don't want to overkill my query as well. There must be some simple answer to this.

I can achieve needed truncation in MSSQL Server with query below:

DELETE FROM pages WHERE id NOT IN (
    SELECT Versions.id FROM
        (SELECT TOP (10) q.id FROM pages AS q WHERE pages.uid = q.uid ORDER BY q.version DESC) 
    AS Versions
)

Thanks

Upvotes: 0

Views: 734

Answers (3)

Sagar Gangwal
Sagar Gangwal

Reputation: 7965

DELETE PG FROM pages PG
LEFT JOIN 
 (
    SELECT  q.id FROM pages AS q ORDER BY q.version DESC LIMIT 10 OFFSET 0
 ) Versions ON Versions.ID=PG.ID
WHERE Versions.ID IS NULL

You can try above query.

Upvotes: 1

Caius Jard
Caius Jard

Reputation: 74660

You don't need to co-ordinate the queries together here; just select a list of IDs to delete- the tables are the same

DELETE FROM pages WHERE id NOT IN 

    (SELECT TOP (10) id FROM pages ORDER BY q.version DESC)

You can't "order by" a delete query..

If youre after the MySQL version, and your version of MySQL doesnt support LIMIT for subqueries, and it doesn't support window functions like this:

delete from pages where id in 
  (select id from 
    (select id, row_number() over(order by dt desc) as rown from pages) a where a.rown > 10) 
  )

then you could consider something like:

CREATE table x as SELECT * FROM pages ORDER BY q.Version DESC LIMIT 10

TRUNCATE /*or delete from*/ pages

INSERT INTO pages SELECT * FROM x

Upvotes: 1

Manasa Chakka
Manasa Chakka

Reputation: 121

DELETE p FROM pages AS p WHERE p.id IN (
    SELECT Versions.id FROM
        (SELECT q.id FROM pages AS q WHERE p.uid = q.uid ORDER BY q.version DESC LIMIT 10) 
    AS Versions
)

Check if this works..

Upvotes: 0

Related Questions