Insert 'even numbered' rows into an existing table in mysql

Question

I have a table:

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| fooID          | int(11)      | NO   | PRI | NULL    | auto_increment | 
| fooDetails     | varchar(200) | YES  |     | NULL    |                | 
| fooListingID   | int(10)      | YES  |     | NULL    |                | 
| fooStatus      | tinyint(4)   | YES  |     | 0       |                | 
+----------------+--------------+------+-----+---------+----------------+

I would like to merge data from a similar table with this table but I would like the data to be alternating so that existing data in this table will all be odd "fooID" and the new inserts will all be the even "fooID".

Ideas?

Answer 1

You can insert the result of a select statement. Use modulo for alternating ids:

INSERT INTO NewTable
SELECT column1, column2
FROM OldTable
WHERE NOT id % 2

Answer 2

I've interpreted your problem as you make to make all the existing fooIDs odd, and then merge into that table some new, even fooIDs.

You can accomplish this fairly easily:

#first make all the existing ids odd
UPDATE oldtable SET fooID=fooID*2-1;

#now insert rows from the other table, making sure the ids are even    
INSERT INTO oldtable (fooID,fooDetails,fooListingID,fooStatus)
    SELECT fooID*2,fooDetails,fooListingID,fooStatus FROM newtable;