Can anyone help with this db2 query

[sowbug]

Can anyone help with this db2 query that I have to rewrite for mysql since it doesn't support subselects and interprets EXISTS differently please? By the way, anyone know when mysql4.1 binaries will be
available?????
--------------------------
INSERT INTO NAMEX_M
SELECT
'493N', NAME_LAST, NAME_FIRST, NAME_MI , SSN, '0'
, 'CNVTD', 'CNVTD', DATE_SYSTEM
, NAME_SUFX
FROM HNAME_X A
WHERE
NAME <> 'COMMENTS CNVTD'
AND
NOT EXISTS (
SELECT 1 FROM NAMEX_M B
WHERE A.SSN = B.SSN
AND A.NAME_LAST = B.NAME_LAST
AND A.NAME_FIRST = B.NAME_FIRST
AND A.NAME_MI = B.NAME_MI
AND A.NAME_SUFX = B.NAME_SUFFIX
)
AND A.DATE_SYSTEM = (SELECT
MAX(DATE_SYSTEM) FROM HNAME_X C
WHERE A.SSN = C.SSN
)

 

[swampBoogie]

This can only be accomplished in mysql by uisng multiple queries and temporary tables.

A not exists predicate may be rewritten as an outer join but you will have some problem as the table you insert into is referenced in select specification and this is not allowed by Mysql.

I don't have any ready solution but you need to start from the bottom and populate a temp table with the result of the

SELECT
MAX(DATE_SYSTEM) FROM HNAME_X C
WHERE A.SSN = C.SSN

query and uisng a group by to get max for all ssn values. Building from this you can proceed with each step. Seems that you have rather much to do in order to get this working in Mysql.