I added a new column named
roomNumber to my
hotel table which already has some values. But when I try inserting new values into
hotel the values would get inserted from where the current data finished rather than at the beginning.
INSERT INTO hotel (roomNumber) VALUES (20), (60), (100), (20), (20), (150), (50), (50), (30), (50);
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
INSERT data you will create new rows for the new data. If you want to change existing rows you need to
UPDATE those rows.
So, to update your three existing rows you need three
UPDATE hotel set roomNumber = 20 where id=8 UPDATE hotel set roomNumber = 60 where id=9 UPDATE hotel set roomNumber = 100 where id=10
I’ve assumed your primary key column is called
id – you may have a different name for it.
Then to insert your remaining data you can run an
INSERT hotel (name, roomNumber) values ('Excelsior',20), ('Manor Park Hotel', 20) etc.
I’ve invented some hotel names here since it seems to make little sense to insert a room count with no hotel name.
Note that ‘beginning of the table’ has no real meaning in SQL. The order of rows is not guaranteed unless you specify an
ORDER BY clause, but that’s not relevant here. To update a specific row you need to specify a
WHERE clause that identifies that row. The Primary Key is a common way to do that.
* You could do the three updates with a single
UPDATE query if you use a suitable
WHERE clause, but that seems needlessly complicated for three rows.
When using SQL
insert you are inserting new rows into a table. You want to update the existing rows, then run an insert for the rest of the new data.
So you want to insert the data from ID 1?
You need to use
INSERT. As the rows already exist.