ON DUPLICATE KEY ALTER TABLE

Question

I am trying to run the following mysql query, I am not getting any errors , but its also not working either?. I just want it to update the column name if a duplicate is detected. thanks :o)

 <?php
$form_id = $_POST[form_id];
    $query = mysql_query(<<<EOT
    ALTER TABLE `email_history` ADD `$form_id` VARCHAR( 255 ) NOT NULL)
    ON DUPLICATE KEY ALTER TABLE `email_history` CHANGE `$form_id` `$form_id` VARCHAR( 255 ) NOT NULL
    EOT
    ) or die(mysql_error());
        ?>

Edit >>>>>>> The only reason I was trying to update was so that the script doesn't error when the form is edited. What is supposed to do is when the email template is created, it adds a column to the email history table, when a user sends an email using the template a date is recorded under that column. The problem occurs when the email template is edited as the create column script tries to re create the column for the template in email history and I get an error, ideally when edited the template would just see that the column already existed and did not try to re create it.

Are you trying to dynamically add a column to that table, and change its type if that column it already existed? If so, you need to rethink your design (unless this is a run-once, upgrade type process but it sure doesn't look like it). — Mat, Jan 01 '12 at 19:04
Hi, I dont want to change the column, its supposed to run once and create the column and thats it, the problem is when the form is edited, the create script tries to re create the column again, ideally if the column existed the script would just not try to re create it. — Iain Simpson, Jan 01 '12 at 19:07
Then query the `information_schema.columns` table to see if the column exists. That `alter table` pumping in a form post parameter as a column name really looks fishy. — Mat, Jan 01 '12 at 19:15

score 3 · Answer 1 · answered Jan 01 '12 at 19:00

3

There's no such thing as ALTER TABLE ON DUPLICATE KEY. You should do it in two steps.

Retrieve table structure.
Construct and run appropriate query.

answered Jan 01 '12 at 19:00

Sergio Tulentsev

226,338
43
373
367

Is there an alternative, like on duplicate column name do nothing ?, as I dont actually want to update the column name as it stays the same, but I also dont want a duplicate column warning every time I save . – Iain Simpson Jan 01 '12 at 19:02

score 1 · Answer 2 · answered Jan 01 '12 at 19:01

1

ON DUPLICATE KEY is not valid for an ALTER TABLE query. See the docs for more info: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

answered Jan 01 '12 at 19:01

Adam Wagner

15,469
7
52
66

ON DUPLICATE KEY ALTER TABLE

2 Answers2