Any idea with regards to deleting a record if the data has no match value in the table. Kindly refer to below example.
The first data below will not be deleted since its having a match value in other row. The only difference is the msgtype. My only concern is to delete the data if its only single record and equals to msgtype = '0430'. Thanks people.
delete from table where msgtype='0430' and not exists (select * from table as t2 INNER JOIN table as t1 ON t2.msgtype = '0210' and t1.msgtype = '0430' AND t2.Trandate = t1.TranDate AND t2.traceno = t1.traceno and t2.tid = t1.tid and t2.cbcode = t1.cbcode)
leinard28 .. Your query is incorrect based on LoztInSpace information.
LoztInSpace original solution is ..
delete t1 from table t1 where msgtype = '0430' and not exists ( select null from table t2 where t1.traceno = t2.traceno and t1.cbcode = t2.cbcode and t1.trantime = t2.trantime and t1.tid = t2.tid and t1.trancode = t2.trancode and t1.tranamt = t2.tranamt )