-1

This is how my table looks like and i want to now add a new row at the top with serialNo. "1" :
This is how my table looks like and i want to now add a new row at the top with serialNo. "1"

Makyen
  • 31,849
  • 12
  • 86
  • 121
Anant Mishra
  • 11
  • 2
  • Possible duplicate of [PHP mySQL - Insert new record into table with auto-increment on primary key](https://stackoverflow.com/questions/7492145/php-mysql-insert-new-record-into-table-with-auto-increment-on-primary-key) – Shashwat Feb 06 '18 at 04:41
  • No it's a different question, i want to add the data directly from localhost/phpmyadmin – Anant Mishra Feb 06 '18 at 04:44

2 Answers2

0

First you need to increment all the rows' serial numbers by one to make room for the new row:

UPDATE noticepdf
SET `serialNo.` = `serialNo.` + 1
ORDER BY `serialNo.` DESC;

The ORDER BY is so that the rows are updated from bottom to top; otherwise the top row will be incremented from 1 to 2, which will fail because there is already a row with 2.

Now you can just insert the row, specifying the serial number as 1:

INSERT INTO noticepdf (`serialNo.`, ...) VALUES (1, ...);

Where ... is replaced with your other fields.

The above can be wrapped up in a stored procedure as so:

DELIMITER $$
CREATE PROCEDURE insert_noticepdf (noticeNumber TEXT, pdf BLOB)
BEGIN
  UPDATE noticepdf
    SET `serialNo.` = `serialNo.` + 1
    ORDER BY `serialNo.` DESC;
  INSERT INTO noticepdf (`serialNo.`, noticeNumber, pdf)
    VALUES (1, noticeNumber, pdf);
END$$
DELIMITER ;
rovaughn
  • 1,213
  • 15
  • 24
  • Can't update table 'noticepdf' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. – Anant Mishra Feb 06 '18 at 04:58
  • Did you run the commands through phpMyAdmin? Which part of phpMyAdmin did you use to run them? I haven't used it in a while but if you run those commands in the SQL tab it may work. – rovaughn Feb 06 '18 at 05:13
  • and also you last line "ALTER TABLE noticepdf AUTO_INCREMENT = 4;" can't be used because i always want to add new row at the top – Anant Mishra Feb 06 '18 at 05:13
  • i ran you suggestion through sql tab and it worked so i added it to the triggers tab so that it will work before every insertion i make to give way for a new row at the top but it doesn't work that way – Anant Mishra Feb 06 '18 at 05:15
  • Do you mean that all future INSERTs to the table should add a new row to the top? Unfortunately that won't be possible unless every time you did an insert you shifted all the serial numbers forward one. Or is this running from an INSERT trigger? – rovaughn Feb 06 '18 at 05:15
  • Oh I see. I will have to think about how it could work from a trigger. – rovaughn Feb 06 '18 at 05:16
  • I added the code you gave into trigger "BEFORE INSERT UPDATE noticepdf SET `serialNo.` = `serialNo.` + 1 ORDER BY `serialNo.` DESC;" – Anant Mishra Feb 06 '18 at 05:21
  • So it seems according to the [MySQL docs](https://dev.mysql.com/doc/refman/5.7/en/stored-program-restrictions.html) you can't modify the table from the trigger: "A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger." You could create a stored procedure however that would do the updating and insertion for you. – rovaughn Feb 06 '18 at 05:30
  • I never worked with stored procedures, so if you can help ? – Anant Mishra Feb 06 '18 at 05:34
  • I added an example of what the stored procedure would look like in plain SQL to my answer. phpMyAdmin probably has a tab that would make it easier to add however. – rovaughn Feb 06 '18 at 05:48
  • I tried adding the code for stored procedure you gave and it still doesn't work. – Anant Mishra Feb 06 '18 at 06:27
  • How does it not work? Does the stored procedure fail to get created? Or does it fail to execute? I forgot to mention but you have to call it with `CALL insert_noticepdf(noticeNumber, pdf);` where `noticeNumber` and `pdf` are replaced with your desired values. – rovaughn Feb 06 '18 at 06:36
  • I will tell you the exact situation, i created a table and i want to add new row at top and move other rows down with their ids increasing by 1. Here comes your solution of stored procedure which takes arguments and as i am inserting through INSERT tab so i removed the arguments and called the procedure from trigger. Still error is "Can't update table 'noticepdf' in stored function/trigger because it is already used by statement which invoked this stored function/trigger." – Anant Mishra Feb 06 '18 at 06:44
  • Oh I see. Yeah it's not possible to do it from a trigger, whether through a stored procedure or not. However you can use the stored procedure instead of an INSERT to do it. – rovaughn Feb 06 '18 at 06:50
0
 Existing table table1 has autoincrement column id and create a temp table with same table structure and insert the value you want to insert at top and then insert all record from existing table table1 
then drop table1 and rename temp table temp01 to table1


   SELECT TOP 1000 [id]
          ,[name]
          ,[age]
      FROM [Mydb].[dbo].[table1]


      CREATE TABLE temp01 (
        ID int NOT NULL IDENTITY(1,1)PRIMARY KEY,
        [name] varchar(255) NOT NULL,

        Age int

    );
      INSERT INTO temp01 values('d',40)
    INSERT INTO temp01  (name,age) SELECT name,age FROM table1;

    drop table table1

    exec sp_rename 'temp01', 'table1'
Akhil Singh
  • 700
  • 6
  • 17