OT: MySQL mass updates

[Alex Beamish]

On 6/5/06, William O'Higgins Witteman <william.ohiggins-H217xnMUJC0sA/PxXw9srA at public.gmane.org> wrote:
>
> I have a database that needs updating, and I just wanted to see if
> anyone has done something like this before.
>
> Here's the problem - I have a constant string in one column that needs
> to be updated to a different string, for all record.
>
> In pseudocode, what I want to do is this:
>   foreach row in table
>     in column A, swap "something" with "somethingelse" leaving the rest
>     of the column's content alone
>
> I think that this could be done with a single, fairly simple command,
> but I'd hate to screw up the whole database by getting it wrong.
>
> Anyone have any suggestions?  Thanks.


To simply update a column for an entire table,

  UPDATE table SET column = someTransformation(column);

Since there's no WHERE clause, this operates over the entire table.

If the transformation is more complicated, you may need to write a short
Perl script to do the same thing .. the advantage to that is you can do the
transformation and check that it works first, without actually updating the
database.


Now in terms of being able to go back in case you got it wrong, there are a
number of options.

1. Backup the table first, and restore it if things go wrong;
2. Create another column, and copy the original column to the new column,
and mess with the new column;
3. In Postgres, I would bracket my attempts with BEGIN WORK and COMMIT (if
it worked) or ROLLBACK (if not). I'm not as aware of whether that's offered
in MySQL yet, and which version of MySQL you have.


Finally, make sure your test ("Did it work?') is ironclad -- you don't want
to make the change then discover it's wrong for male students born in
Saskatchewan. Or something. Good luck.

-- 
Alex Beamish
Toronto, Ontario
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://gtalug.org/pipermail/legacy/attachments/20060606/8b535fcb/attachment.html>