I have 2 virtually identical tables and wish to move data between them.
Basically one table is called "live_table" and the other is named
"suspended_table" and the only difference is that the primary key in the
"suspended_table" is an auto incremented integer where as "live_table"
primary key is just a standard integer.

Here's the life-cycle:
1. Record gets entered into "suspended_table"
2. Record checked and then inserted into "live_table" with now corresponding
record deleted in "suspended_table"
3. Record (after period of time) is moved back into "suspended_table"

Here's now my question. Does the primary key integer value change when it
moves back to the suspended table (as specified in point 3)? Will it see the
returning record as simply a new one, overwrite the existing primary key
value and give it the next available? Is there a way stopping assigning an
auto incremented number if a value already exists?

I have some ideas for workarounds such as actually not deleting the record
in the suspended table but instead adding an extra field to act as a flag.
This I assume can be set or unset by the use of a trigger. Never tried this
but read these work "when an INSERT, UPDATE, or DELETE statement is issued
against the associated table or when database system actions occur."*

Cheers

Phil

* taken from:
http://www.experts-exchange.com/Data..._20958686.html

Phil Latio wrote:
> I have 2 virtually identical tables and wish to move data between them.

> I have some ideas for workarounds such as actually not deleting the record
> in the suspended table but instead adding an extra field to act as a flag.

Unless this flag causes you some dramatic speed issues to your queries
or other problems, I would suggest you to do this, but perhaps a little
differently than you are planning. Instead of just adding a flag to your
first table, add the flag and remove the other table complitely. Use the
flag to identify the location for the record, or even history also. The
flag could for example have values 0=new in suspend, 1=in live table,
2=back to suspend. Or what ever fills your needs.

There are a lot of benefits with the flag-style when compared to
identical tables.

For one, instead of insert-delete-insert-delete you only need to do
update-update, which is much faster and more safe (lesser chance for
data loss), your database structure would also be more simple, if you
had only one table, instead of two identical. Queries that need data
from both tables would also be propably faster. And you wouldn't need to
lock tables during the "move" as it happens within single query, to
avoid problems that might appear in multi-user environments. Also moving
several or all records within a single query would be trivial.

So instead of adding triggers to your delete queries, I would suggest
you to modify the database and rewrite your delete-insert queries to a
single and fast update queries. Unless of course this is for some reason
problematic.

>I have 2 virtually identical tables and wish to move data between them.
>Basically one table is called "live_table" and the other is named
>"suspended_table" and the only difference is that the primary key in the
>"suspended_table" is an auto incremented integer where as "live_table"
>primary key is just a standard integer.
>
>Here's the life-cycle:
>1. Record gets entered into "suspended_table"
>2. Record checked and then inserted into "live_table" with now corresponding
>record deleted in "suspended_table"
>3. Record (after period of time) is moved back into "suspended_table"

Why not add a column `status` which has the value 'Active' or 'Suspended',
and merge the two tables permanently?
1. Record gets entered with `status` = 'Suspended'
2. Record checked and then its status changed to 'Active'
3. Record status is changed back to 'Suspended' after a period of time.

>Here's now my question. Does the primary key integer value change when it
>moves back to the suspended table (as specified in point 3)?

It depends on how you move it. If you INSERT a value of the
auto-incremented integer that is not null, you get that value
unchanged, unless it's a duplicate, in which case the INSERT fails.

>Will it see the
>returning record as simply a new one, overwrite the existing primary key
>value and give it the next available? Is there a way stopping assigning an
>auto incremented number if a value already exists?

Consider what happens when you restore a dump made by mysqldump
of a table with an auto-incremented field. The INSERT statements
insert a specific integer (not null, and not leaving the field out of
the INSERT's field list). That's what goes into the record. It would
be a real mess if the auto-incremented field got reassigned, and make
mysqldump pretty useless for backups.

>I have some ideas for workarounds such as actually not deleting the record
>in the suspended table but instead adding an extra field to act as a flag.

I think you need to merge the two tables, with the status flag. The
`status` field may need its own index, or become part of a compound index
with something else.

Gordon L. Burditt

"Gordon Burditt" <gordonb.3wcat@burditt.org> wrote in message
news:124t8u0dj6e3hd0@corp.supernews.com...
> >I have 2 virtually identical tables and wish to move data between them.
> >Basically one table is called "live_table" and the other is named
> >"suspended_table" and the only difference is that the primary key in the
> >"suspended_table" is an auto incremented integer where as "live_table"
> >primary key is just a standard integer.
> >
> >Here's the life-cycle:
> >1. Record gets entered into "suspended_table"
> >2. Record checked and then inserted into "live_table" with now
corresponding
> >record deleted in "suspended_table"
> >3. Record (after period of time) is moved back into "suspended_table"
>
> Why not add a column `status` which has the value 'Active' or 'Suspended',
> and merge the two tables permanently?
> 1. Record gets entered with `status` = 'Suspended'
> 2. Record checked and then its status changed to 'Active'
> 3. Record status is changed back to 'Suspended' after a period of time.
>
> >Here's now my question. Does the primary key integer value change when it
> >moves back to the suspended table (as specified in point 3)?
>
> It depends on how you move it. If you INSERT a value of the
> auto-incremented integer that is not null, you get that value
> unchanged, unless it's a duplicate, in which case the INSERT fails.
>
> >Will it see the
> >returning record as simply a new one, overwrite the existing primary key
> >value and give it the next available? Is there a way stopping assigning
an
> >auto incremented number if a value already exists?
>
> Consider what happens when you restore a dump made by mysqldump
> of a table with an auto-incremented field. The INSERT statements
> insert a specific integer (not null, and not leaving the field out of
> the INSERT's field list). That's what goes into the record. It would
> be a real mess if the auto-incremented field got reassigned, and make
> mysqldump pretty useless for backups.
>
> >I have some ideas for workarounds such as actually not deleting the
record
> >in the suspended table but instead adding an extra field to act as a
flag.
>
> I think you need to merge the two tables, with the status flag. The
> `status` field may need its own index, or become part of a compound index
> with something else.

Thanks for the info, especially regarding how auto-increment works. That's
very useful to know.

However I am bit confused as to why I should merge the tables. Someone else
suggested it too but if I am simply going to add a flag (which switches the
record on or off), then that should suffice shouldn't it? As I said though,
if both you and the other chap suggest the same thing, then I am most likely
missing a trick here somewhere.

Once again thanks for taking the trouble to answer.

Cheers

Phil

Phil Latio wrote:

> However I am bit confused as to why I should merge the tables. Someone else
> suggested it too but if I am simply going to add a flag (which switches the
> record on or off), then that should suffice shouldn't it? As I said though,
> if both you and the other chap suggest the same thing, then I am most likely
> missing a trick here somewhere.

Your current situation is something like this:

create table a(
id int unsigned
name text,
age int unsigned,
flag tinyint(1) );

create table b(
id int unsigned
name text,
age int unsigned );

insert into a values(1,'Jack',25);
insert into a values(2,'Jill',24);

Now, to move data from a to b and back from b to a requires these queries:
lock tables a,b;
select * from a where id in(1,2);
// parse data in program and create queries on application side
insert into b values(1,'Jack',25);
insert into b values(2,'Jill',24);
update a set flag=1 where id in(1,2);
unlock tables;

lock tables a,b;
update a set flag=0 where id in(1,2);
delete from b where id in(1,2);
unlock tables;

####
Now, assume that you would merge these tables and add single flag:
create table a(
id int unsigned
name text,
age int unsigned,
flag tinyint(1) );

insert into a values(1,'Jack',25,0);
insert into a values(2,'Jill',24,0);

Now, to move data "from a to b and back"
update a set flag=1 where id in(1,2);
update a set flag=0 where id in(1,2);


Now, what advantages does this have:
- it was faster to write the example
- it has less queries -> less code and complexity to your program
- it is faster to move data
- requires less power from cpu (server and client)
- requires less memory from the server and the client
- requires less hard drive space from the server
- is less vulnerable to programming errors (someone might easily forget
for example to lock tables which might cause problems),
- is less vulnerable to system errors (less calls to database, less
action with hard drive -> less errors)
- doesn't require client to lock tables (=faster in multi-user systems),
- the database structure is more simple which means less learning time
for someone new to the project.
- Faster to check whether item is in either of the tables.

Disadvantages:
- Query to search items in only in a or b is slightly slower, but with
an index on the flag-column, this shouldn't be an issue.

Do you now understand better, or do you think I missed some advantages
or disadvantages?

>Thanks for the info, especially regarding how auto-increment works. That's
>very useful to know.
>
>However I am bit confused as to why I should merge the tables. Someone else
>suggested it too but if I am simply going to add a flag (which switches the
>record on or off), then that should suffice shouldn't it?

Why find a direct route when going from New York to San Francisco through
Moscow and Melbourne (4 times each) is sufficient?

>As I said though,

You WILL have queries that need to look at both tables regardless
of the status. Some of this is likely to be for reports wanted by
management, like how many records were active each month last year.
Another situation will be where the customer calls in and wants to
find out why his account isn't working: there's a big difference
between the record not being there at all and the record being
suspended.

Moving the record from one table to another requires multiple queries
(at least one to insert and one to delete the old one) and therefore
locking that's easy to forget, while changing the flag only requires
one.

Using the flag does not require changing the code that marks the
record active and inactive if a field is added to the table, but
copying it will.

Unique indexes will not protect you across two tables, so if you
manage to get the same record ID in both tables things will screw up
from there when you try to move the record.

>if both you and the other chap suggest the same thing, then I am most likely
>missing a trick here somewhere.
>
>Once again thanks for taking the trouble to answer.

Gordon L. Burditt