SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Delete if No same value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

leinad28
Starting Member

Philippines
4 Posts

Posted - 09/06/2013 :  00:55:20  Show Profile  Reply with Quote
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

939 Posts

Posted - 09/06/2013 :  01:23:32  Show Profile  Reply with Quote
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

Philippines
4 Posts

Posted - 09/06/2013 :  01:59:14  Show Profile  Reply with Quote
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

939 Posts

Posted - 09/06/2013 :  02:03:18  Show Profile  Reply with Quote
"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

146 Posts

Posted - 09/11/2013 :  13:55:04  Show Profile  Reply with Quote
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

Philippines
4 Posts

Posted - 09/14/2013 :  00:25:48  Show Profile  Reply with Quote
@tm, got it. thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000