Hi All,
I'm trying to do a rather complex comparison/update on my data and I'm looking to do it with 1 query. Basically, I have 2 tables. Table "A" contains accnt_id_1, accnt_id_2 and accnt_hold_flag. Table "B" is a validation table containing accnt_id_1 and accnt_id_2. I would like to update all the accnt_hold_flag flags in table "A" to "X" where accnt_id's 1 & 2 do not exist in table "B". Here is what I got so far...
UPDATE A
LEFT OUTER JOIN B ON (A.accnt_id_2 = B.accnt_id_2 ) AND (A.accnt_id_1 = B.accnt_id_1)
SET accnt_hold_flag = 'X'
WHERE B.accnt_id_1 Is Null
AND B.accnt_id_2 Is Null
CF doesn't seam to like "LEFT".
And for the bonus question (I will award an extra 150 for this)...if accnt_id_2 is equal to "1001M" or "1001X", I would like to convert it to "10010" before the validation check. I don't want to update the actual data just validate against "10010".
Thanks in advance. I have to go away so I might not get to reward the response until Monday morning.
Mike
Start Free Trial
