MySQL: null becomes 0 in INSERT and SELECT statement
I am trying to do an INSERT statement using SELECT query statement into a table with two columns (item_id that is not null and price that can be null). The item_id is not auto-increment and I would do a select query from another table. So an example would be:
INSERT INTO myTable
SELECT ((SELECT id FROM myCatalogue where name="Item X"), "500");
By doing just the SELECT statement alone, if Item X does not exist in list, the query would return:
| SELECT id FROM myCatalogue where name="Item X" | 500 | | NULL | 500 |
However, when the whole INSERT + SELECT statement, the following is inserted into 'myTable' instead:
| item_id | price | | 0 | 500 |
I want MySQL to prompt me an error when item does not exist in 'myCatalogue', such as 'Cannot insert the value NULL in column item_id' so i can go back to 'myCatalogue' to verify if this item exists. However it automatically change the value from NULL to 0 and inserts successfully. Hence I would like to ask how do I need to modify the statement such that it does not change NULL to 0?
This is my table definition:
CREATE TABLE myTable
(
item_id int(11) unsigned NOT NULL,
price float unsigned DEFAULT NULL,
PRIMARY KEY (item_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
INSERT INTO myTable
SELECT id, "500"
FROM myCatalogue
WHERE name = "Item X"
Is easier to work with. You could confirm if insert was successful through last_insert_id() .
You can try with:
INSERT INTO myTable
SELECT id, "500"
FROM myCatalogue
WHERE name = "Item X"
UNION
SELECT NULL, "500"
Obviously, id needs to be NULL enabled and default , which can be difficult if it's PRIMARY . If you have PRIMARY or UNIQUE and wants an error instead, you can run like this:
INSERT INTO myTable
( SELECT id, "500"
FROM myCatalogue
WHERE name = "Item X")
UNION
( SELECT item_id, "500"
FROM myTable)
LIMIT 1
Aslong as id is PRIMARY , it will error over the fact you're trying to insert existing id into the table.
I'm beginner, it work in theory. That's for mysql.
INSERT INTO myTable SELECT ((SELECT IFNULL(id,0) as id FROM myCatalogue where name="Item X"), "500");
That's for mssql.
INSERT INTO myTable SELECT ((SELECT ISNULL(id,0) as id FROM myCatalogue where name="Item X"), "500");
If not work, that's my mistake.
链接地址: http://www.djcxy.com/p/63802.html上一篇: 如何加入一对多的关系表？