CODEWITHSUNDEEP
mysqlsql-insertalter
Yozachar
Yozachar

Reputation: 398

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

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.

Upvotes: 1

Views: 111

Answers (1)

Dark Knight
Dark Knight

Reputation: 6531

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.

Upvotes: 2

Related Questions