Results 1 to 13 of 13

Thread: Delete problem

  1. #1
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32

    Question Unanswered: Delete problem

    Have table with two cols:

    pkg int,
    eqp int

    with ex. values:

    Code:
    pkg  eqp
       1    1
       1    1
       2    3
       2    3
       2    3
       3    1
       4    1
       4    1
       4    1
    Need to delete some records, so at end eqp column says how many records with the same pkg - from example above need to delete one record with pkg 1 and two with pkg 4. Want to do this without cursor. Any help appreciated.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Logic is not clear - could you add some details?

  3. #3
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32
    Ok - back to example:

    we have two records with pkg = 1 (for equal pkg, eqp will be also equal), both of them have eqp = 1 - which means I need only one of those records - other one should be deleted. There are also 3 records with pkg = 3, they have eqp = 3 - which means it's ok (3 records, eqp = 3). For pkg = 3 it's also ok., but for pkg = 4, eqp = 1 (so only one record with pkg = 4 should stay, other 2 should be deleted).

    Does it make clear?

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Code:
    use pubs 
    go
    create table mytable99(pkg  int,eqp int)
    GO
    insert into mytable99 
    select 1,1
    UNION ALL
    select 1,1
    UNION ALL
    select 2,3
    UNION ALL
    select 2,3
    UNION ALL 
    select 2,3
    UNION ALL
    select 3,2
    UNION ALL
    select 3,2
    UNION ALL
    select 3,2
    UNION ALL
    select 3,2
    UNION ALL
    select 4,1
    UNION ALL
    select 4,1
    UNION ALL
    select 4,1
    UNION ALL
    select 4,1
    GO
    SELECT * FROM	MYTABLE99 
    GO
    DROP TABLE MYTABLE99
    GO
    Current resultset
    pkg eqp
    ----------- -----------
    1 1
    1 1
    2 3
    2 3
    2 3
    3 2
    3 2
    3 2
    3 2
    4 1
    4 1
    4 1
    4 1

    Needed resultset
    pkg eqp
    ----------- -----------
    1 1
    2 3
    2 3
    2 3
    3 2
    3 2
    4 1

    Let the deletes begin

    I am working on it ... this is for help of other guys
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What about package 2?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32
    Originally posted by Brett Kaiser
    What about package 2?
    Both in my and Enigma examples no deletion is needed. For pkg = 2 eqp = 3, so max 3 records with pkg = 2 allowed (as it is).

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by MST78
    Both in my and Enigma examples no deletion is needed. For pkg = 2 eqp = 3, so max 3 records with pkg = 2 allowed (as it is).
    Do you have id column for this table?

  8. #8
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32
    Originally posted by snail
    Do you have id column for this table?
    Nope. Addition of ID column is possible - but for some reasons I'd like to avoid that. With ID it wouldn't be such problem for me.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Sledge hammer anyone?

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    GO
    
    CREATE TABLE myTable99(pkg  int,eqp int)
    GO
    
    INSERT INTO mytable99(pkg, eqp) 
    SELECT 1,1 UNION ALL
    SELECT 1,1 UNION ALL
    SELECT 2,3 UNION ALL
    SELECT 2,3 UNION ALL 
    SELECT 2,3 UNION ALL
    SELECT 3,2 UNION ALL
    SELECT 3,2 UNION ALL
    SELECT 3,2 UNION ALL
    SELECT 3,2 UNION ALL
    SELECT 4,1 UNION ALL
    SELECT 4,1 UNION ALL
    SELECT 4,1 UNION ALL
    SELECT 4,1
    GO
    
    SELECT * FROM myTable99
    GO
    
    DECLARE @MIN_pkg int, @MAX_pkg int, @eqp int, @sql varchar(8000)
    
    CREATE TABLE #myTemp99(pkg  int,eqp int)
    
    SELECT @MIN_pkg = MIN(pkg),@MAX_pkg = MAX(pkg)
      FROM myTable99
    
    WHILE @MIN_pkg <> @MAX_pkg
      BEGIN
    	SELECT TOP 1 @eqp = eqp FROM myTable99 WHERE pkg = @MAX_pkg
    
    	SELECT @SQL = 'INSERT INTO #myTemp99(pkg, eqp)'
    	+ ' SELECT TOP ' + CONVERT(varchar(3),@eqp) 
    	+ 'pkg, eqp FROM myTable99 WHERE pkg = ' + CONVERT(varchar(3),@MAX_pkg)
    
    	EXEC(@SQL)
    
    	SELECT    @MAX_pkg = MAX([pkg])
    	  FROM	  myTable99
    	 WHERE	  [pkg] < @MAX_pkg
    
      END
    
    SELECT TOP 1 @eqp = eqp FROM myTable99 WHERE pkg = @MIN_pkg
    
    SELECT @SQL = 'INSERT INTO #myTemp99(pkg, eqp)'
    + ' SELECT TOP ' + CONVERT(varchar(3),@eqp) 
    + 'pkg, eqp FROM myTable99 WHERE pkg = ' + CONVERT(varchar(3),@MIN_pkg)
    
    EXEC(@SQL)
    
    SELECT * FROM #myTemp99
    GO
    
    DROP TABLE #myTemp99
    DROP TABLE myTable99
    GO
    
    SET NOCOUNT OFF
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32
    Thanks Brett I'll check it at once.

  11. #11
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by MST78
    Nope. Addition of ID column is possible - but for some reasons I'd like to avoid that. With ID it wouldn't be such problem for me.
    You may try to redesign your table (or tables) because in future you'll have much more problems than this one. What Brett did just confirms this.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by snail
    You may try to redesign your table (or tables) because in future you'll have much more problems than this one. What Brett did just confirms this.
    I agree 100%.

    And what I gave you is totally arbitrary.

    It assumes that all your data is alike based on the package

    I just thought it was a neat exercise...

    god what a geek....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    32
    I have no possibility to redesign this table (since it's not under my care) - I may only ask to add an ID column. I know it's ill-designed.

    Thx for all

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •