SQL insert new values into new column from the beginning of table

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.

my query

INSERT INTO hotel
(roomNumber) 
VALUES
    (20),
    (60),
    (100),
    (20),
    (20),
    (150),
    (50),
    (50),
    (30),
    (50);

The values started inserting from where id = 10 , rather I want it to insert where id = 1 (at the beginning)

Contents hide

Answers:

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.

Method 1

If you 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 queries^*:

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 query:

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.

Method 2

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.

UPDATE statement with multiple WHERE conditions

Method 3

So you want to insert the data from ID 1?

You need to use UPDATE, not INSERT. As the rows already exist.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating