Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2016
    Posts
    3

    Unanswered: Deleting rows with a subquery

    So I am by no means a SQL expert, but I know my way a query or two...

    I am trying to sort a table and then delete duplicate rows. I have tried a multitude ways to do this, but DB2 is just not like anything I have worked with. I first tried with a CTE:

    With Worknumber (RNum) as
    (Select rownumber() over(partition by WNTRACKNUM order by WNMATCHACT desc) as RNum from RRC.WORKIMP)
    Delete from Worknumber where RNum > 1;


    Then I tried a sub query:
    Delete Rnum from
    (Select rownumber() over(partition by WNTRACKNUM order by WNMATCHACT desc) as DupNum from RRC.WORKIMP) as RNum
    where DupNum > 1;

    Both of these work beautifully in MySQL and TSQL, but throw a SQL 199 error in DB2.

    What do I need to change to make DB2 happy?

    Bill

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    "Delete Rnum from ..."?
    What a strange syntax?
    DB2 uses "DELETE FROM ..." syntax.
    Try this:
    Code:
    declare global temporary table session.test(i int) on commit preserve rows not logged;
    
    insert into session.test values 1,1,1,2,2,3;
    
    delete from (
    select rownumber() over (partition by i) rn
    from session.test
    )
    where rn>1;
    
    select * from session.test;
    
     I
     -
     1
     2
     3
    Regards,
    Mark.

  3. #3
    Join Date
    Feb 2016
    Posts
    3

    Unhappy Tried that...

    Quote Originally Posted by mark.b View Post
    "Delete Rnum from ..."?
    What a strange syntax?
    DB2 uses "DELETE FROM ..." syntax.
    Try this:
    Code:
    declare global temporary table session.test(i int) on commit preserve rows not logged;
    
    insert into session.test values 1,1,1,2,2,3;
    
    delete from (
    select rownumber() over (partition by i) rn
    from session.test
    )
    where rn>1;
    
    select * from session.test;
    
     I
     -
     1
     2
     3

    Hi Mark:

    Thanks for the response, but I tried that too. I get a SQL 104 error. It doesn't like the subquery in the delete statement.

    ERROR: A character, token, or clause is invalid or missing.

    [SQL0104] Token ( was not valid. Valid tokens: <IDENTIFIER>.
    Error Code: -104

    Query = Delete from
    (Select rownumber() over(partition by WNTRACKNUM order by
    WNMATCHACT desc) as DupNum from RRC.WORKIMP) as RNum where DupNum
    > 1;

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Post your DB2-version + fixpack and the operating-system that runs the DB2-server. These facts should always be the first things in your post when asking for specific help.

  5. #5
    Join Date
    Feb 2016
    Posts
    3

    Version

    I am running DB2 on an IBM System i, OS v7R1mo, Package 5770ST1.

Posting Permissions

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