Delete row data where condition

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

General SQL Server Forums

New to SQL Server Programming

Delete row data where condition

Author

Topic

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-12-28 : 04:32:56

Hi all,

I have a table as below:
RecordID is a running number.
I want to delete the row of record while status IS NULL and ID1, ID2, ID3 is same ID1, ID2, ID3 from a batchid (example:Batchid3).

RecordID ID1 ID2 ID3 status BatchID
_____________________________________________________________
1 xxx1 yyy1 zzz1 NULL Batchid1
2 xxx2 yyy2 zzz2 NULL Batchid1
3 xxx1 yyy1 zzz1 Prov Batchid2
4 xxx2 yyy2 zzz2 Prov Batchid2
5 xxx1 yyy1 zzz1 NULL Batchid3
6 xxx2 yyy2 zzz2 NULL Batchid3

after execute: below is the result that i need to be.
which row 1,2 should be deleted after i run batchid3.

RecordID ID1 ID2 ID3 status BatchID
_____________________________________________________________
3 xxx1 yyy1 zzz1 Prov Batchid2
4 xxx2 yyy2 zzz2 Prov Batchid2
5 xxx1 yyy1 zzz1 NULL Batchid3
6 xxx2 yyy2 zzz2 NULL Batchid3

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-28 : 04:41:31

Hi
you expect this?

DELETE FROM <Table_Name> WHERE status IS NULL

-------------------------
R...

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-12-28 : 04:59:56

quote:
Originally posted by rajdaksha

Hi
you expect this?

DELETE FROM <Table_Name> WHERE status IS NULL

-------------------------
R...

Hi rajdaksha,

Not only like that.
i will pass in a parameter example batchid3.
So i will check the whole table whether is there any row of record where status IS NULL and ID1, ID2, ID3 same with the record from bathid3. Sure not delete the record of Batchid3.

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-28 : 05:18:17

HI,

declare @tab table(RecordID int,ID1 varchar(10), ID2 varchar(10), ID3 varchar(10), status varchar(10), BatchID varchar(10))
insert into @tab

select 1, 'xxx1' ,'yyy1','zzz1', NULL,'Batchid1' union all
select 2 ,'xxx2' ,'yyy2','zzz2' ,NULL,'Batchid1' union all
select 3 ,'xxx1' ,'yyy1','zzz1','Prov','Batchid2' union all
select 4 ,'xxx2' ,'yyy2','zzz2','Prov','Batchid2' union all
select 5, 'xxx1','yyy1','zzz1', NULL ,'Batchid3' union all
select 6 ,'xxx2' ,'yyy2','zzz2', NULL,'Batchid3'
--
delete t from (
select RecordID,id1,id2,id3,status,batchid,row_number() over(partition by id1,id2,id3,status order by RecordID desc ) as row FROM @tab
) as t
where row = 2
select * from @tab

Thanks,
vikky.

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-12-30 : 04:18:33

quote:
Originally posted by vikky

HI,

declare @tab table(RecordID int,ID1 varchar(10), ID2 varchar(10), ID3 varchar(10), status varchar(10), BatchID varchar(10))
insert into @tab

select 1, 'xxx1' ,'yyy1','zzz1', NULL,'Batchid1' union all
select 2 ,'xxx2' ,'yyy2','zzz2' ,NULL,'Batchid1' union all
select 3 ,'xxx1' ,'yyy1','zzz1','Prov','Batchid2' union all
select 4 ,'xxx2' ,'yyy2','zzz2','Prov','Batchid2' union all
select 5, 'xxx1','yyy1','zzz1', NULL ,'Batchid3' union all
select 6 ,'xxx2' ,'yyy2','zzz2', NULL,'Batchid3'
--
delete t from (
select RecordID,id1,id2,id3,status,batchid,row_number() over(partition by id1,id2,id3,status order by RecordID desc ) as row FROM @tab
) as t
where row = 2
select * from @tab

Thanks,
vikky.

hI vikky, thanks your helping.
i have 2 question as below:
1. Do i need to delete or remove the table @tab after this?
2. If the second user also using the same function, does it overwrite the data at @table which inserted by first user?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-30 : 04:25:41

1.No you didn't drop the @tab table variable.
2.It holds the values only for the current session. Just like a ordinary variable.

Read about table variable in BOL for more details.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources