PHP, MySQL: Duplicate series of rows, but change 1 column?

Question

I have a table called scheduler_sched which has several columns, including a column called schedule_id.

I need a function where I can pass 2 ids (copy_from_id, copy_to_id) as parameters. And what I need to do is take every row where schedule_id = copy_from_id AND duplicate it but change the copy_from_id to the copy_to_id

So basically I want to to the equivalient of this:

UPDATE scheduler_sched SET schedule_id = 32 WHERE schedule_id = 28

Only I do not want to UPDATE any rows, I want to create duplicates with the new ID's

Does this make sense?

How can I do this?

THANKS!

(By the way schedule_id is not a unique/index field on this table)

Answer 1

Just INSERT a new row instead of updating. SELECT first if that schedule_id 28 exists, and if it does, insert a new one with that being the ID.

Answer 2

I think that ON DUPLICATE KEY UPDATE syntax may help you:

http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

e.g.:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

Answer 3

Since you haven't specified a version of MySQL, I'm going to assume that it is the lastest (5.4).

Assuming I am understanding you correctly, you should be able to implement this using triggers: http://dev.mysql.com/doc/refman/5.4/en/create-trigger.html

One of the benefits of using triggers, is it is all handled by the database itself.

Answer 4

Insert into scheduler_sched (column1, column2, column3,schedule_id ) 
Select column1, column2, column3, 32 from scheduler_sched WHERE schedule_id = 28