delete query help - 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 2005 Forums

Transact-SQL (2005)

delete query help

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-01-22 : 05:53:13

Dear All,

Can u please some body help me to write delete query for the folowing condition
:

my table is

ID MOD description
84 NULL Calcel
84 26 Select
85 NULL nothing to do

In this case for the duplicate records of ID, i need to check for MOD value if it is NULL then i need to delete and need to retain for 26 in the table itself.

The table is having 15,000 records with some ID is duplicated and some are not duplicated.
Please help in this regard.

Thanks,
Gangadhar

Kristen
Test

22859 Posts

Posted - 2010-01-22 : 06:18:25

Do you mean this?


SELECT D.*
-- DELETE D
FROM MyTAble AS D
WHERE [MOD] IS NULL
AND EXISTS
(
    SELECT *
    FROM MyTable AS T2
    WHERE T2.ID = D.ID
          T2.[MOD] IS NOT NULL
)

Run the SELECT first to check what is going to be deleted, then you can use the Delete. Backup first!!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-22 : 06:18:52

Lol


DECLARE @foo TABLE (
	[ID] INT
	, [MOD] INT
	, [DESCRIPTION] VARCHAR(255)
	)

INSERT @foo
      SELECT 84, NULL, 'Calcel'
UNION SELECT 84, 26, 'Select'
UNION SELECT 85, NULL, 'Nothing to do'

SELECT * FROM @foo

DELETE f
FROM
	@foo f
WHERE
	f.[MOD] IS NULL

	AND EXISTS (
		SELECT 1
		FROM @foo f2
		WHERE
			f2.[ID] = f.[ID]
			AND f2.[MOD] IS NOT NULL
		)

SELECT * FROM @foo

Great minds...

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-01-22 : 06:31:22

Dear All one more addition

suppose the data is lie this
ID MOD description
46 NULL cancel
46 TC cancel
46 26 select

Here i need to delete again TC and NULL MOD rows and need to select only 26 MOD row ...this applies for above as well..

if there is no duplicate even if MOD is null i should't delete that records

Thanks,
Gangadhar

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-22 : 06:36:48

What do you want to do for this case?
ID MOD description
46 NULL cancel
46 TC cancel

??

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-01-22 : 06:38:17

Here i need to delete again TC and NULL [MOD] rows and need to select only 26 [MOD] row

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-22 : 07:09:05

No -- you didn't understand.

What do you want to do when the only rows are:

46 NULL
46 TC

And there is no 46 26 row.

Or can that never happen?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-01-22 : 07:16:29

Hi

My data will be like this..

ID MOD description
84 NULL Calcel
84 26 Select
85 NULL nothing to do

46 NULL cancel
46 TC cancel
46 26 select

For the case ID=46 here i need to select only one row with MOD=26 only other 2 rows needs to be deleted

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-22 : 07:30:54

Maybe this? I'm expecting another "but wait -- what if my data is like <ANOTHER SCENARIO YOU DIDN'T MENTION>"


/*
ID MOD description 
84 NULL Calcel 
84 26 Select 
85 NULL nothing to do 

46 NULL cancel 
46 TC cancel 
46 26 select 
*/

DECLARE @foo TABLE (
	[ID] INT
	, [MOD] VARCHAR(50)
	, [DESCRIPTION] VARCHAR(255)
	)

INSERT @foo
      SELECT 84, NULL, 'Calcel'
UNION SELECT 84, '26', 'Select'
UNION SELECT 85, NULL, 'Nothing to do'

UNION SELECT 46, NULL, 'cancel'
UNION SELECT 46, 'TC', 'cancel'
UNION SELECT 46, '26', 'select'

SELECT * FROM @foo

DELETE f
FROM
	@foo f
WHERE

	EXISTS (
		SELECT 1
		FROM @foo f2
		WHERE
			f2.[ID] = f.[ID]
			AND f2.[MOD] = '26'
		)

	AND (
		f.[MOD] IS NULL
		OR f.[MOD] = 'TC'
	)

SELECT * FROM @foo

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-01-22 : 09:40:34

Thanks you very much for your answer..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources