SQL delete script - 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

General SQL Server Forums

New to SQL Server Programming

SQL delete script

Author

Topic

johanm
Starting Member

5 Posts

Posted - 2014-09-06 : 13:55:19

Hallo I need some help to make a correct SQl script.
I have a table, FrmAcc, which have the following columns:
Frm, FrmGr

Example

Frm FrmGr
1001 1
1001 10
1001 11
1002 1
1002 10
1002 99
1003 1
1003 99

I would like to delete the followingf records:
Alle Frm which do have FrmGr=99, but not delete the FrmGr = 99 and FrmGr = 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-09-06 : 14:53:50

[code]DELETE f
FROM (
SELECT MAX(CASE WHEN FrmGr = 99 THEN 1 ELSE 0 END) OVER (PARTITION BY Frm) AS Valid
FROM dbo.FrmAcc
) AS f
WHERE Valid = 1
AND FrmGr NOT IN (1, 99);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

johanm
Starting Member

5 Posts

Posted - 2014-09-06 : 15:00:11

Thanks for Your suggestion, however some of the records have FrmGr=99999 so I do not think MAX wil work

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-09-06 : 15:12:54

quote:
Originally posted by johanm

Thanks for Your suggestion, however some of the records have FrmGr=99999 so I do not think MAX wil work

Post representative sample data.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-06 : 19:39:03

Sorry had a little more time after the kids when to bed

SELECT * FROM #JOHANM

Results --

FRM FRMGR
1001 1
1001 10
1001 11
1002 1
1002 10
1002 99
1003 1
1003 99
1001 99999
1001 99999
1001 99999

first solution --

BEGIN TRAN
DELETE #JOHANM
FROM (
SELECT MAX(CASE WHEN FrmGr = 99 THEN 1 ELSE 0 END) OVER (PARTITION BY Frm) AS Valid
FROM #JOHANM
) AS f
WHERE Valid = 1
AND FrmGr NOT IN (1, 99);

results --
FRM FRMGR
1001 1
1002 1
1002 99
1003 1
1003 99

It looks like Tara Kizer solution still works,

mine solution works, but it seems silly to do, but for giggles

BEGIN TRAN
DELETE #JOHANM
FROM (
SELECT MAX(CASE WHEN FrmGr like '[9]%' THEN 1 ELSE 0 END) OVER (PARTITION BY Frm) AS Valid
FROM #JOHANM
) AS f
WHERE Valid = 1
AND FrmGr NOT IN (1, 99);

Results --

FRM FRMGR
1001 1
1002 1
1002 99
1003 1
1003 99

Since "The MAX() function returns the largest value of the selected column."

Question -
I would like to delete the followingf records:
Alle Frm which do have FrmGr=99, but not delete the FrmGr = 99 and FrmGr = 1

I am not sure I understand the question.....It seems like it readS delete * frm where frmgr = 99, but then it says BUT not delete frmgr = 99.....

Sorry I guess I did not fully understand the question

johanm
Starting Member

5 Posts

Posted - 2014-09-07 : 02:58:14

Thanks for your suggestions, how can I make a "selection" of the same, so I can se which rows will be deleted before I delete them?

johanm
Starting Member

5 Posts

Posted - 2014-09-07 : 04:34:17

I tried the different solutions, but it did not work. I think this is because I did not give you all information.

The table consist of 36000 rows

In column FrmGr are a lot of different numbers (ex. 1, 10, 99, 1011230, 12345, 999999)

Any help for solutions?

Chris_Kelley
Posting Yak Master

114 Posts

Posted - 2014-09-07 : 12:29:08

johanm
Starting Member

5 Posts

Posted - 2014-09-07 : 14:54:32

The script does not work

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources