As for the second 'bonus' issue... this is actually a bigger problem which I don't think you've given enough details for. Is the string always going to be either a number, or a number with a single character on the end? If so, then this should be pretty simple:
UPDATE TableA
SET accnt_hold_flag = 'X'
WHERE NOT EXISTS
(
SELECT TOP 1 accnt_id_1
FROM TableB
WHERE accnt_id_1 = TableA.accnt_id_1
AND
CASE WHEN ISNUMERIC(accnt_id_2) = 1 THEN accnt_id_2 ELSE LEFT(accnt_id_2,LEN(accnt_
CASE WHEN ISNUMERIC(TableA.accnt_id_
)
If there can be any number of characters (ie, not always zero or 1) I'll have to think about it...
Main Topics
Browse All Topics
by: TallerMikePosted on 2003-05-23 at 14:52:45ID: 8575064
UPDATE TableA
SET accnt_hold_flag = 'X'
WHERE NOT EXISTS
(
SELECT TOP 1 accnt_id_1
FROM TableB
WHERE accnt_id_1 = TableA.accnt_id_1
AND accnt_id_2 = TableA.accnt_id_2
)
This will update all hold_flags in TableA, where there are no records in TableB havinging a matching set of account 1 and 2 numbers.
If you meant that you want to update TableA anytime there is no account 1 OR account 2 numebr found in TableB, then you'd want this:
UPDATE TableA
SET accnt_hold_flag = 'X'
WHERE NOT EXISTS
(
SELECT TOP 1 accnt_id_1
FROM TableB
WHERE accnt_id_1 = TableA.accnt_id_1
OR accnt_id_2 = TableA.accnt_id_2
)