0

I want to make a script that moves one row from one table to another table when this is been done I want the ID to change and use the new one in the new table.

Because at the moment when I move the row from one table to another table it says duplicate ID as it was already used but now is deleted.

One table is main table the other is archive, when I click restore from the PHP script it will move the archive back to the main database.

Tables

<---Main Database--->         <---Archive--->
ID                            ID
Title                         Title

if ID in archive is 127 but I have created a new record in the Main Database after archiving it it can not be restored as the ID 127 is in use by the main database. resulting in the Duplicate ID issue.

user2071435
  • 19
  • 8
  • 7
    Please share the related table structures and your queries – 1000111 Nov 03 '16 at 09:56
  • 1
    Can't you just omit the id-column when you move the row? – M. Eriksson Nov 03 '16 at 09:58
  • 2
    If the ID column is an auto-increment column in the new table, all you need to do is omit it from the list of columns/values you are adding to that table. – PaulF Nov 03 '16 at 09:59
  • PHP Script or Mysql query? – barudo Nov 03 '16 at 10:00
  • 1
    Possible duplicate of [In MySQL, can I copy one row to insert into the same table?](http://stackoverflow.com/questions/4039748/in-mysql-can-i-copy-one-row-to-insert-into-the-same-table) – LeonardChallis Nov 03 '16 at 10:01
  • What do you want to happen in the circumstances where ID 127 is already in the main table? If you describe that (in words) you'll describe your answer that you just need to put into code. (Alternatively, don't reuse IDs) – Robbie Nov 04 '16 at 05:19

1 Answers1

2

Try this:

INSERT into 
    new_table (
        `column1`, `column2` -- and all other columns except id
    )
(SELECT `column1`, `column2` -- and all other columns in old_table except id
FROM
    old_table
-- if you have WHERE clause do it here...
)
barudo
  • 665
  • 4
  • 13