how to convert SELECT result to be an UPDATE

[romina drees]

Hi @all,

have a very specific problem with an update of a foreign key. It has been set wrong during input and I want to correct it. Finding the right foreign key is easy, but the update itself not.

problem:
table a contains key, number as fields
table b contains a.key as foreign key, but some entries need an update

SELECT a.key, b.key FROM b JOIN a ON number = (SELECT a.number FROM a WHERE a.key=b.key) AND a.key > 65 WHERE b.key = ... ;

the subquery has 2 rows in the result and the entry with value > 65 is retrieved. So altogether this query works well and gives me the right result, but now I want to update b.key with the right key and tried:

UPDATE b JOIN a ON number = (SELECT a.number FROM a WHERE a.key=b.key) AND a.key > 65 SET b.key = a.key WHERE b.key = ...;

After one hour running I stoped it because maybe there is an mistake, any ideas?

Thanks a lot!

P.S.:
Original examples:
select spectrum.peak_id, spectrum.spectrum_id, spectra.spectrum_id from spectrum join spectra on spec_no = (select spectra.spec_no from spectra where spectra.spectrum_id = spectrum.spectrum_id) and spectra.spectrum_id > 65 WHERE spectrum.peak_id > 9882600 and spectrum.spectrum_id = 1;

-> Query took about a minute, because of testing spectrum_id was set to 1 but it can be up to 65.

update spectrum join spectra on spec_no = (select spectra.spec_no from spectra where spectra.spectrum_id = spectrum.spectrum_id) and spectra.spectrum_id > 65 set spectrum.spectrum_id = spectra.spectrum_id WHERE spectrum.peak_id > 9882600 and spectrum.spectrum_id = 1;

--> took an hour, before I quit it