bonaca bonaca - 1 year ago 48
MySQL Question

move all to another table except id

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.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download