Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Delete if No same value

Author  Topic 

leinad28
Starting Member

4 Posts

Posted - 2013-09-06 : 00:55:20
Hi Everyone,

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.

msgtype traceno cbcode trantime tid trancode tranamt
0210 000318 0022 110532 00010001 011000 500.00
0430 000318 0022 110532 00010001 011000 500.00

I need to delete this record since it doesn't have a same data with 0210 msgtype. Thanks
0430 000450 0075 131118 00010001 841000 1.00

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-09-06 : 01:23:32
delete from table where msgtype='0430' and not exits (select null from table t2 where table.traceno=t2.traceno and table.cbcode=t2.cbcode ..... )
Go to Top of Page

leinad28
Starting Member

4 Posts

Posted - 2013-09-06 : 01:59:14
Hi,

Its not working. I tried this:

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)

=(..anyway thanks for your input.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-09-06 : 02:03:18
"It's not working" doesn't give me much to help you with.....
You don't need the inner join within your not exists.
Go to Top of Page

tm
Posting Yak Master

160 Posts

Posted - 2013-09-11 : 13:55:04
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
)

Is this what is required?
Go to Top of Page

leinad28
Starting Member

4 Posts

Posted - 2013-09-14 : 00:25:48
@tm, got it. thanks
Go to Top of Page
   

- Advertisement -