move all to another table except id

Question

move a record from posts to archive

$stmt = $db->prepare(
    "insert into archive (date, art, img, vid, title, subtitle, story, tags, status, user, pos, titlex, inde)
    select date, art, img, vid, title, subtitle, story, tags, status, user, pos, titlex, inde from posts
    where id = :aid"
);

don't tell me that there is no option like:
move * except id...

id is not movable due to a possible colision with archive's ids.

Just curious to know , why you want this. If you need you can use join to get data ? — Niklesh Raut, Aug 17 '17 at 08:15
@Shadow Unless I completely misread this question, it's nothing like the one you flagged it as a duplicate of. — Nick Coons, Aug 17 '17 at 08:20
Move is an `insert ... select ...` statement, therefore the duplicate applies. — Shadow, Aug 17 '17 at 10:54

score 1 · Accepted Answer · answered Aug 17 '17 at 08:18

There is no option like move.. you'd need to insert it into one table and then delete it from the other.

However, you may want to reconsider your table design. If you want to archive something, rather than having two separate tables that contain the same type of data, you should add a flag to your posts table to indicate whether or not an entry is archived or not. Something like this:

ALTER TABLE posts ADD archived BOOLEAN DEFAULT 0;

When a post is normally added and it's not archived, the archived column is set to 0. When you want to archive something, you just run an UPDATE statement setting it to 1 for that record.

move all to another table except id

1 Answers1