sql - MySQL 5.6 Error 1054: Unknown column in 'field list' on INSERT -
i'm using mysql workbench create , manage comic book store database. i'm having problem inserting rows comic_issue table. problem i'm having same problem many others posted questions here none of fixes seem working me. here information on comic_title , comic_issue tables. list comic_title table because 'name' column part of composite primary key in comic_issue.
mysql> describe comic_title; +-------------------+-------------+------+-----+---------+-------+ | field | type | null | key | default | | +-------------------+-------------+------+-----+---------+-------+ | name | varchar(45) | no | pri | null | | | creator_creatorid | int(11) | no | pri | null | | | isactive | tinyint(1) | no | | 1 | | +-------------------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> select * comic_title; +----------+-------------------+----------+ | name | creator_creatorid | isactive | +----------+-------------------+----------+ | avengers | 1 | 1 | | batman | 2 | 1 | | ironman | 3 | 1 | +----------+-------------------+----------+ 3 rows in set (0.00 sec) mysql> describe comic_issue; +-----------------------+--------------+------+-----+---------+-------+ | field | type | null | key | default | | +-----------------------+--------------+------+-----+---------+-------+ | comic_title_name | varchar(45) | no | pri | null | | | volumenum | int(11) | no | pri | null | | | coverdate | date | no | pri | null | | | publisher_publisherid | int(11) | no | mul | null | | | inventoryqty | int(11) | no | | null | | | coverprice | decimal(4,2) | no | | null | | | issuetitle | varchar(45) | yes | | null | | | synopsis | longtext | yes | | null | | | format | varchar(45) | yes | | null | | | storyarc | varchar(45) | yes | | null | | +-----------------------+--------------+------+-----+---------+-------+ 10 rows in set (0.00 sec) here inserts i've tried:
mysql> insert `cbdb`.`comic_issue` values ('batman', 1, '2011-11-01', (select publisherid publisher name = 'marvel'), 100, 2.99, 'the new 52!', null, null, 'court of owls'); error 1054 (42s22): unknown column 'comic_title_name' in 'field list' mysql> insert comic_issue values ('ironman', 10, '2013-05-15', (select publisherid publisher name = 'marvel'), 100, 3.99, 'the secret origin of tony stark', null, null, 'the secret origin of tony stark'); error 1054 (42s22): unknown column 'comic_title_name' in 'field list' mysql> insert `cbdb`.`comic_issue` values (null, 1, '2011-11-01', (select publisherid publisher name = 'marvel'), 100, 2.99, 'the new 52!', null, null, 'court of owls'); error 1048 (23000): column 'comic_title_name' cannot null this last error interesting. tried inserting null value (not allowed) , complained column can't null sees column. please note i've tried number of different ways, "insert comic_issue" (i.e. without backticks , without specifying database.) tried listing columns. tried putting 'batman' in double-quotes , no quotes @ all. nothing doing it. suggestions welcome.
i think trigger creates problem.so try one..
create trigger `update_mailbox` after insert on comic_issue each row begin insert mailbox_has_comic_issue values ((select mailbox_mailboxid mailbox_has_comic_title mailbox_has_comic_title.comic_title_name = new.comic_title_name), (select mailbox_customer_customerid mailbox_has_comic_title mailbox_has_comic_title.comic_title_name = new.comic_title_name), new.comic_title_name, new.volumenum,new.coverdate); end$$ two keywords unique sql statements while defining triggers i.e; old , new.
these keywords allow refer data before , after event triggers.
check once...
Comments
Post a Comment