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

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)

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

So you want to insert the data from ID 1?

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