delete - 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 2000 Forums

SQL Server Development (2000)

delete

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-07-07 : 09:16:02

Hi,
This query returns two rows.
phonebookidfeed is null

select phonebookidfeed, firstname, lastname from tblPhoneBookFeedNlnd
group by phonebookidfeed, firstname, lastname
having count(isnull(phonebookidfeed,''))>1

now I would like to delete these two records

I need something to delete these records. can you correct this query please?
delete from tblPhoneBookFeedNlnd
select phonebookidfeed, firstname, lastname from tblPhoneBookFeedNlnd
group by phonebookidfeed, firstname, lastname
having count(isnull(phonebookidfeed,''))>1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-07 : 09:18:46

[code]Delete from tblPhoneBookFeedNlnd where phonebookidfeed in
(select phonebookidfeed from tblPhoneBookFeedNlnd group by phonebookidfeed,
firstname, lastname having count(isnull(phonebookidfeed,''))>1[/code]

Madhivanan

Failing to plan is Planning to fail

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-07-07 : 09:21:47

will this work even if the phoneBookidfeed is null ?
Thanks

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-07-07 : 09:24:02

quote:

Originally posted by madhivanan

Delete from tblPhoneBookFeedNlnd where phonebookidfeed in 
(select phonebookidfeed from tblPhoneBookFeedNlnd  group by phonebookidfeed, 
firstname, lastname having count(isnull(phonebookidfeed,''))>1

Madhivanan

Failing to plan is Planning to fail

I just tried this but it does not work.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-07 : 09:25:33

Can you give some sample data?

Madhivanan

Failing to plan is Planning to fail

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-07-07 : 09:42:51

NULL NULL NULL
NULL daniels jack
NULL judge mike

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-07 : 09:55:30

quote:
This query returns two rows.
phonebookidfeed is null

select phonebookidfeed, firstname, lastname from tblPhoneBookFeedNlnd
group by phonebookidfeed, firstname, lastname
having count(isnull(phonebookidfeed,''))>1

I dont think your query will return two rows for those data
Can you post actual data you used?

Madhivanan

Failing to plan is Planning to fail

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-07-07 : 16:13:33

I have solved the problem by using a temp table and joing it to the main table.
Thanks for your time guys.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-08 : 00:54:09

quote:
Originally posted by fmardani

I have solved the problem by using a temp table and joing it to the main table.
Thanks for your time guys.

Well
Can you post your solution so that everyone knows it?

Madhivanan

Failing to plan is Planning to fail

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-07-08 : 04:48:53

--do not process the duplicates, so delete them...
--this method is used since there may be no ID...
select phonebookidfeed, firstname, lastname
into #TEMPtblPhoneBookFeedHP
from tblPhoneBookFeedHP
group by phonebookidfeed, firstname, lastname
having count(isnull(phonebookidfeed,'')) > 1

delete from tblphonebook
where isnull(phoneBookIDfeed,'') in (select isnull(phoneBookIDfeed,'') from #TEMPtblPhoneBookFeedHP)
and tblphonebook.firstname in (select firstname from #TEMPtblPhoneBookFeedHP)
and tblphonebook.lastname in (select lastname from #TEMPtblPhoneBookFeedHP)
and CompanyID = @CompanyID

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-08 : 05:05:31

Well
See if this delete query works


delete T1 from tblphonebook T1
inner join #TEMPtblPhoneBookFeedHP T2 on
isnull(T1.phoneBookIDfeed,'') = isnull(T2.phoneBookIDfeed,'') 
and T1.firstname =T2.firstname 
and T1.lastname =T2.lastname 
where T1.CompanyID = @CompanyID

Madhivanan

Failing to plan is Planning to fail

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-07-11 : 04:49:28

Great.
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources