Update on related table (subselect)

[Wojtek K]

Hello,

I'm not sure if this forum is accurate, but it's more or less related to Oracle SQL.

is there any way to conduct an update based on other table values, in MySQL 4?

exemple:
table t1:
id int(22), value1 decimal(15,4))

table t2:
id int(22), value2 decimal(15,4))

id is unique.


I'd like to make an update on column t1.value1, replcing them by t2.value2 for each t1.id=t2.id.

On Oracle is similar to :
------------------
BEGIN
DECLARE
i PLS_INTEGER;

BEGIN

FOR i IN (SELECT t2.id, MAX(value2) AS value2
FROM t2
GROUP BY t2.id)

LOOP

UPDATE t1
SET t1.value1 = i.value2
WHERE t1.id = i.id;

END LOOP;

COMMIT;
END;

END;

Trying to do this on MySQL I get an error. Is it possible to do such a thing?

I will be grateful for all sugestions
Thanks in advance.

Regards
Wojtek



Edited 1 time(s). Last edit at 10/10/2005 04:46AM by Wojtek K.