Delete if No same value - SQL Server Forums

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 ..... )

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.

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.

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?

leinad28
Starting Member

4 Posts

Posted - 2013-09-14 : 00:25:48

@tm, got it. thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources