mysql update

I am currently writing a series of scripts to handle a quiz. As usual when i write these things I try and implement little things that I may have not known about before, or that I have never had a chance to use.

When doing and ‘Insert’ query, you can do multiple inserts. Why isn’t it the same for ‘Update’? It would make life a lot easier.

*sigh*

Note to self: when trying to update multiple rows remember to include the relevant identifier otherwise the script won’t know what to update and throw a tantrum.

This entry was posted in Rants. Bookmark the permalink.

2 Responses to mysql update

  1. John says:

    There IS a way – but you’re right, not by doing a straight update.

    Instead you need to create an alternative table and fill with the desired values, and then use an update query to applay the changes. You can do this with no more than 3 queries like this:

    CREATE TEMPORARY TABLE blah (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    `keys` INT NOT NULL ,
    `text` VARCHAR( 50 ) NOT NULL ,
    ); #Create a temporary table with the id and field you wish to change

    INSERT INTO BLAH (keys,text) VALUES (3,’FIRE’),(5,’BALLS’); #insert your proposed changes into the temporary table

    UPDATE mywords SET mywords.text = blah.text WHERE mywords.index = blah.index; #now use the new table to apply the changes with one update.

    I’m using mySQL 5, so I’m not sure about how mySQL 4 .x deals with temporary tables, but there it is. I hope it works.

  2. Rich says:

    Simple idea, sounds good, must use at some point!

    Going back thorugh things i have written to try and optimise is not something I am good at – but at some point I really need to.

    Hopefully I can utilise this then, many many thanks for the tip!

Comments are closed.