Re: Changing a UPDATE tbl1... FROM tbl1 MSSQL query
This worked.
UPDATE SECM_SECURITY_UNIVERSE s2
INNER JOIN BOND_CORE_INFORMATION bc ON s2.ASSET_ID = bc.ASSET_ID
INNER JOIN FI_ISSUER_INFORMATION fii ON bc.ISSUER_ID = fii.ISSUER_ID
INNER JOIN BOND_ISSUANCE_SUMMARY bis ON bis.ASSET_ID = bc.ASSET_ID
LEFT OUTER JOIN CONVERTIBLE_BOND_INFO cv ON cv.ASSET_ID = s2.ASSET_ID
LEFT OUTER JOIN PREFERRED_STOCK_INFORMATION ps ON ps.ASSET_ID = s2.ASSET_ID
LEFT OUTER JOIN FI_DOMAIN_VALUES fd ON bis.SECOF_CHAR = fd.GCODE_VALUE_CD AND
GCODE_TABLE_NAME = 'CDS_SECOFF_CHAR'
SET s2.NAME = CONCAT(RTRIM(SUBSTRING(UPPER(IFNULL(IFNULL(fii.ISSUER_SHORT_NAME, fii.ISSUER_LONG_NAME),
CONCAT(CONVERT('NO NAME, TICKER: ' USING latin1), CONVERT(fii.ISSUER_TICKER USING latin1)))),1,36)),
CONVERT(IFNULL(CONCAT(' ', CAST(TRIM( TRAILING '.' FROM TRIM(TRAILING '0' FROM bc.CURRENT_CPN)) AS CHAR(13)), '%'), '') USING latin1),
CONVERT(IFNULL(CONCAT(' ', CAST(bc.MATURITY_DATE AS CHAR(10))), ' PERP') USING latin1),
CONVERT(IFNULL(CONCAT(' SR:', RTRIM(SUBSTRING(bc.SERIES,1,6))), '') USING latin1),
CONVERT((CASE WHEN cv.ASSET_ID IS NULL THEN '' ELSE ' CVT' END) USING latin1),
CONVERT((CASE WHEN ps.ASSET_ID IS NULL THEN '' ELSE ' PFD' END) USING latin1),
CONVERT((CASE WHEN fd.GCODE_VALUE_DESCR IS NULL OR INSTR(s2.NAME, '144A') OR INSTR(s2.NAME, '(REGS)') THEN '' ELSE
CONCAT(' ', fd.GCODE_VALUE_DESCR) END) USING latin1) COLLATE latin1_swedish_ci)
WHERE s2.ASSET_ID > 0 AND s2.NAME IS NULL
Things to look out for are where there are potential for NULL in a OUTER JOIN condition, use <=> if a NULL is acceptable in both conditions. Not a condition you see here.
David