Hi,
Please find the query that will delete the record for ConsumerID = 99 from table A.
BEGIN TRAN
SELECT * FROM TableA
Delete TableA from TableA a
where ConsumerID = 99 and
exists(select 1 from TableB b where a.ConsumerID = b.ConsumerID and b.DeleteFlag = 1)
SELECT * FROM TableA
ROLLBACK/COMMIT