On Monday 19 December 2005 14:09, [EMAIL PROTECTED] wrote: > > 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'; > > 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;
Thanks. I'll try this. I found another way that seem to work: SELECT @x:=category_id FROM category WHERE name='undefined'; UPDATE albums SET [EMAIL PROTECTED] WHERE category_id=0; But your suggestions is a bit more elegant... :-) -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
