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

unknown column in 'field list' python
unknown column in 'field list mysql insert
unknown column in 'field list' mysql select
unknown column in 'field list' java
unknown column in 'field list' hibernate
unknown column in 'field list' spring boot
unknown column in 'field list' php
unknown column in 'field list' sequelize

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

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.

"Unknown column in 'field list'", but column does exist, There is an unprintable character 30 (RecordSeparator) inserted between purchase_date and the ' in the INSERT statement. Just remove the  DROP TABLE IF EXISTS `transactions`; CREATE TABLE `transactions` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `purchase_date` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEF

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') 

Unobvious "Unknown column in 'field list'" error, The strangeness caused by "Unknown column 'column-name' in 'field list'" message while this column was existing in this table. Summary: if you're seeing error like this one that some of the column doesn't exist in the table but in fact it exists then one of the culprit could be a trigger on this table. The column/variable you are trying to use doesn't exist in the dataset on which you are performing the operation. Do check the variable name for typos.. And in case you are using a variable present in the dataset and yet getting the error, then us

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 in 'field list' error on MySQL Update query , Use different quotes for "y" as the identifier quote character is the backtick (“`”). Or else, MySQL "thinks" that you point to a column named "y". I've been receiving these warnings off-and-on for about a year. It's the strangest thing - they appear even after executing a command that doesn't even involve the offending variable, and persist across R sessions. The warnings appear randomly, so I haven't been able to reliably reproduce them. The problem has reached a boil in the past two weeks because I'm automatically generating markdown

mysql update query error Code 1054 unknown column in field list , The error message is quite clear. The table krneki_2 does not have a column named id_naslov . Unless there is some corruption in system  I bumped into this error unknown column, the diff is the query is built thru HQL inside session.executeQuery("select id, name, sum(paid), custType from cust group by brand") that's why having to manually type inner join or join keyword is not an option as the hql is the one generating it. it produces a query sumthing like this:

MySQL Bugs: #1689: Unknown column in 'field list' for a field that , mysql> insert into DemoTable798 values(100,Adam); ERROR 1054 (42S22): Unknown column 'Adam' in 'field list'. You need to use single  I don;t seen anything that would cause an unknown column issue unless you don't have name, description, and image fields on your table. However you do not seem to be using single quotes around the values you are inserting, which is problematic as well. – Mike Brant Aug 21 '13 at 21:37 1

Why the #1054, In this tutorial, you will learn how to quickly fix it. This is what the error page looks like: unknown column in Joomla. In your Joomla admin area, go  Warning messages: 1: Unknown or uninitialised column: 'prin_amt'. 2: Unknown or uninitialised column: 'prin_amt'. 3: Unknown or uninitialised column: 'prin_amt'. 4: Unknown or uninitialised column: 'prin_amt'. str(all_matched_source) does its job and gives the same warnings. Now for the real confusion, going back to names(all_matched_source

Comments
  • in your second sub query you are selecting field Id, but no such field exists in third sub query @ptrcao
  • 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];?
  • Wp is the alias, this is same as your original query. This is a valid SQL syntax. You can execute and check it
  • Check this stackoverflow.com/questions/11005209/…
  • 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 :)
  • 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