Jørn Dahl-Stamnes <[EMAIL PROTECTED]> wrote on 12/19/2005 06:10:55 AM:
> I have two tables; > > CREATE TABLE category ( > category_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, > name CHAR(15) NOT NULL, > PRIMARY KEY (category_id) > ); > CREATE TABLE albums ( > album_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, > category_id MEDIUMINT UNSIGNED NOT NULL, > PRIMARY KEY (album_id) > ); > (the other fields are removed for clerity). > > What I'm trying to do is to update all records in the albums table where the > category_id = 0 with a new id-value found by a SELECT statement in the > category table, somethink like: > > SELECT category_id FROM category WHERE name = 'some name'; > UPDATE albums SET category_id='value found from above' > WHERE category_id='some value'; > > But I have not found any way to do this from a script. > > Any hints from some kind souls out there? > > -- > Jørn Dahl-Stamnes > homepage: http://www.dahl-stamnes.net/dahls/ > If I recall correctly (IIRC) from another thread, you are on a fairly recent version of MySQL. That means you should be able to perform a multitable update. These look just like regular JOIN queries except they are flipped around from the SELECT format into the UPDATE format. As a regular SELECT statement: SELECT a.album_id, c.category_id, c.name FROM albums a INNER JOIN categories c ON a.category_id = c.category_id AND a.category_id = 0; If that query returns what you would like your data to look like, you can turn it into an UPDATE statement through a little bit of rearranging: UPDATE albums a INNER JOIN categories c ON a.category_id = c.category_id AND a.category_id = 0 SET a.category_id=c.category_id; For more details on how to use multi-table update statements: http://dev.mysql.com/doc/refman/4.1/en/update.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
