Re: A few tricky queries to migrate

[Stuart Wood]

> SELECT ISSUER_ID = MAX(fi.ISSUER_ID), fi.ISSUER_ORGID

I'm not sure about the rest, cos I'm still on 4.0, so I'm still not fully au fait on MySQL's handling of subqueries, but the above won't work the way I think you think it will...
MySQL will take that as a boolean test... ie. is [implied fi.]ISSUER_ID equal to MAX(fi.ISSUER_ID) - it will return a 1 or 0 depending on the result.

I think you need :
SELECT MAX(fi.ISSUER_ID) ISSUER_ID, fi.ISSUER_ORGID ...etc...

As for the update, you can't UPDATE TABLE SET [settings] FROM [tables].
It's more UPDATE [tables] SET t1.Column=Value;
ie.

UPDATE SECM_SECURITY_UNIVERSE
join ( SELECT DISTINCT e2.RIC, 52 AS TYPE ...etc...) a on SECM_SECURITY_UNIVERSE.SECURITY_ID = a.SEC_ID
set EQU_RIC = a.RIC, ...etc...
where SECM_SECURITY_UNIVERSE.ASSET_ID IS NULL

I can't help on the derived table issues per se, but good luck in finding out and I hope this helps.

Cheers
--
Stu