mySQL - Inserting values into a single colume results in multiple rows?

Question

I'm inserting multiple rows into single column. Here is the table:

CREATE TABLE Book (
   ID INT PRIMARY KEY AUTO_INCREMENT, 
   Title VARCHAR(100), 
   Author VARCHAR(40), 
   Pages INT, 
   Price FLOAT, 
   GENRE VARCHAR(30)
);

+----+---------------------------+----------------+-------+--------+-----------+
| ID | Book_Title                | Author         | Pages | Price  | GENRE     |
+----+---------------------------+----------------+-------+--------+-----------+
|  1 | The Lost Symbol           | Dan Brown      |   500 | 524.75 | Fiction   |
|  2 | Flamingo                  | NCERT          |   235 | 345.55 | Text Book |
|  3 | The Great Zoo of China    | Mathew Reilly  |   312 | 401.75 | Fantasy   |
|  4 | Walking from east to west | Ravi Zacharias |   240 | 220.45 | Biography |
|  5 | The Time Machine          | H.G Wells      |   245 | 120.45 | Novel     |
|  6 | Flamingo                  | NCERT          |   235 | 345.55 | Text Book |
+----+---------------------------+----------------+-------+--------+-----------+

ALTER TABLE Book ADD COLUMN Year INT;
INSERT INTO Book(Year) 
VALUES (1987), (2001), (1997), (1983), (1974), (2001);`

But I am getting totally unexpected results:

+----+---------------------------+-------+--------+-----------+------+
| ID | Book_Title                | Pages | Price  | GENRE     | Year |
+----+---------------------------+-------+--------+-----------+------+
|  1 | The Lost Symbol           |   500 | 524.75 | Fiction   | NULL |
|  2 | Flamingo                  |   235 | 345.55 | Text Book | NULL |
|  3 | The Great Zoo of China    |   312 | 401.75 | Fantasy   | NULL |
|  4 | Walking from east to west |   240 | 220.45 | Biography | NULL |
|  5 | The Time Machine          |   245 | 120.45 | Novel     | NULL |
|  6 | Flamingo                  |   235 | 345.55 | Text Book | NULL |
|  7 | NULL                      |  NULL |   NULL | NULL      | 1987 |
|  8 | NULL                      |  NULL |   NULL | NULL      | 2001 |
|  9 | NULL                      |  NULL |   NULL | NULL      | 1997 |
| 10 | NULL                      |  NULL |   NULL | NULL      | 1983 |
| 11 | NULL                      |  NULL |   NULL | NULL      | 1974 |
| 12 | NULL                      |  NULL |   NULL | NULL      | 2001 |
+----+---------------------------+-------+--------+-----------+------+

PS: I've removed the column Author

Expected Result:

+----+---------------------------+-------+--------+-----------+------+
| ID | Book_Title                | Pages | Price  | GENRE     | Year |
+----+---------------------------+-------+--------+-----------+------+
|  1 | The Lost Symbol           |   500 | 524.75 | Fiction   | 1987 |
|  2 | Flamingo                  |   235 | 345.55 | Text Book | 2001 |
|  3 | The Great Zoo of China    |   312 | 401.75 | Fantasy   | 1997 |
|  4 | Walking from east to west |   240 | 220.45 | Biography | 1983 |
|  5 | The Time Machine          |   245 | 120.45 | Novel     | 1974 |
|  6 | Flamingo                  |   235 | 345.55 | Text Book | 2001 |
+----+---------------------------+-------+--------+-----------+------+

I referred these but no use:

Server version: 5.7.27-0ubuntu0.18.04.1

All help is appreciated.

What is the unexpected part for you? is working as intended. — Juan Carlos Oropeza, Aug 26 '19 at 16:06
Looks like you want `UPDATE` rows already on the db not `INSERT` new ones — Juan Carlos Oropeza, Aug 26 '19 at 16:08
Probably learn the difference between `INSERT` and `UPDATE`. — Eric, Aug 26 '19 at 16:38

Dark Knight · Accepted Answer · 2019-08-26T16:54:49.743

2

Use UPDATE statement instead of INSERT. Insert will create new records and you want to add more info to existing records.

UPDATE Book SET `Year` = 1987 WHERE ID = 1;
UPDATE Book SET `Year` = 2001 WHERE ID = 2;
UPDATE Book SET `Year` = 1997 WHERE ID = 3;
UPDATE Book SET `Year` = 1983 WHERE ID = 4;
UPDATE Book SET `Year` = 1974 WHERE ID = 5;
UPDATE Book SET `Year` = 2001 WHERE ID = 6;

OR

UPDATE Book
SET `Year` = CASE `ID`
                WHEN 1 THEN 1987
                WHEN 2 THEN 2001 
                WHEN 3 THEN 1997 
                WHEN 4 THEN 1983
                WHEN 5 THEN 1974 
                WHEN 6 THEN 2001 
           END
WHERE `ID` IN (1,2,3,4,5,6);

If you don't use ID filter that it is recommended to have else section in case when clause.

edited Aug 26 '19 at 16:54

answered Aug 26 '19 at 16:15

Dark Knight

6,116
1
15
37

Thanks, can I do this using some sort of loop? – Jovial Joe Jayarson Aug 26 '19 at 16:39
You can do a short version too. I will update my answer to that. If this solves your problem, don't forget to accept and do vote up. – Dark Knight Aug 26 '19 at 16:47
Yes please, update answer with short version. I will mark it as answer once it's working, but my Upvotes won't be public till I have 15 Reputation. – Jovial Joe Jayarson Aug 26 '19 at 16:53

mySQL - Inserting values into a single colume results in multiple rows?

1 Answers1