Unknown column 'ID' in 'field list', but the column IS in the list

Question

Error:

Unknown column 'ID' in 'field list'

I am baffled. As you can see below, ID is a column under the wp_posts table:

create table wp_posts (
  ID integer primary key auto_increment,
  post_title varchar(30),
  post_type varchar(30)
);


create table wp_postmeta (
  ID integer primary key auto_increment,
  post_id integer,
  meta_key varchar(30) not null default '_regular_price',
  meta_value integer not null
);

Query yielding the error:

DELETE FROM wp_posts 
WHERE  id IN (SELECT id 
              FROM   (SELECT id, 
                             post_title, 
                             post_type, 
                             meta_value 
                      FROM   (SELECT wp_postmeta.post_id, 
                                     post_title, 
                                     post_type, 
                                     meta_value, 
                                     Row_number() 
                                       OVER( 
                                         partition BY post_title 
                                         ORDER BY wp_postmeta.meta_value) rn 
                              FROM   wp_postmeta 
                                     JOIN wp_posts 
                                       ON wp_postmeta.post_id = wp_posts.id 
                              WHERE  wp_posts.post_type = 'Product' 
                                     AND wp_postmeta.meta_key = '_regular_price' 
                             ) t 
                      WHERE  t.rn <> 1) AS aliasx); 

) t where t.rn <> 1
) AS aliasx
);

dbfiddle here

in your second sub query you are selecting field Id, but no such field exists in third sub query @ptrcao — Anoos, Feb 06 '19 at 12:21

score 3 · Accepted Answer · answered Feb 06 '19 at 12:19

Regardless of database, you have this code:

                 (SELECT id, 
                         post_title, 
                         post_type, 
                         meta_value 
                  FROM   (SELECT wp_postmeta.post_id, 
                                 post_title, 
                                 post_type, 
                                 meta_value, 
                                 Row_number() 
                                   OVER( 
                                     partition BY post_title 
                                     ORDER BY wp_postmeta.meta_value) rn

There is no id in the subquery. Presumably, you intend post_id rather than id.

PSK · Answer 2 · 2019-02-06T14:05:16.347

1

You can simplify your query like following using EXISTS

DELETE wp 
FROM   wp_posts wp 
WHERE  EXISTS (SELECT 1 
               FROM   wp_postmeta md 
               WHERE  md.post_id = wp.id 
                      AND wp.post_type = 'Product' 
                      AND md.meta_key = '_regular_price')

edited Feb 06 '19 at 14:05

answered Feb 06 '19 at 12:37

PSK

17,547
5
32
43

What is the wp instance between DELETE and FROM? I'm not familiar with this syntax, as it doesn't seem to conform to the structure `DELETE FROM table [WHERE conditions];`? – ptrcao Feb 06 '19 at 17:31
Wp is the alias, this is same as your original query. This is a valid SQL syntax. You can execute and check it – PSK Feb 06 '19 at 17:33
1

Check this https://stackoverflow.com/questions/11005209/why-cant-i-use-an-alias-in-a-delete-statement – PSK Feb 06 '19 at 17:35
Ah ok, I'm familiar with aliases, however, I never knew you had to insert the alias between the DELETE and FROM for it to work. How strange, you learn something new every day :) – ptrcao Feb 06 '19 at 17:37
Does `SELECT 1` mean that you are only taking the first instance of the set of matches over post_title, ordered by wp_postmeta.meta_value? That's the part I wasn't sure about, and I just wanted to confirm the equivalence between our codes. Is your code is doing the equivalent of my window function partitoned by post_title? – ptrcao Feb 06 '19 at 17:47
1

No.. it’s just a falg which will return 1 or null and the delete will only work for the rows where it returns 1. You can check Exist in google. It’s very useful and better tha IN clause in many terms – PSK Feb 06 '19 at 17:51

score 0 · Answer 3 · answered Feb 06 '19 at 12:27

Gordon Linoff is right (- I will accept his answer), I accidentally omitted wp_posts.ID from the subquery. Solution is to add wp_posts.ID, to the SELECT list in the lower order subquery so it can be selected in the higher order subquery:

(SELECT wp_posts.ID, wp_postmeta.post_id,post_title,post_type ,meta_value,

Unknown column 'ID' in 'field list', but the column IS in the list

3 Answers3