Results 1 to 3 of 3
  1. #1
    Join Date
    May 2013
    Posts
    3

    Unanswered: deleting with fetch first deletes non unique rows

    Hi,

    I was just curious about this functionality. If I do:

    COL1 COL2
    ----------- --------------------------------
    5 2013-05-29-19.34.02.236000000000
    5 2013-05-29-19.34.03.098000000000
    5 2013-05-29-19.34.03.796000000000
    5 2013-05-29-19.34.04.035000000000
    5 2013-05-29-19.34.04.219000000000
    1 2013-05-29-19.33.56.369000000000
    2 2013-05-29-19.33.58.101000000000
    3 2013-05-29-19.33.59.328000000000
    4 2013-05-29-19.34.00.845000000000
    5 2013-05-29-19.34.04.722000000000

    10 record(s) selected.

    db2 => delete from t1 where col1 in (select col1 from t1 order by col2 fetch fir
    st 1 row only)
    DB20000I The SQL command completed successfully.
    db2 => select * from t1

    COL1 COL2
    ----------- --------------------------------
    5 2013-05-29-19.34.02.236000000000
    5 2013-05-29-19.34.03.098000000000
    5 2013-05-29-19.34.03.796000000000
    5 2013-05-29-19.34.04.035000000000
    5 2013-05-29-19.34.04.219000000000
    2 2013-05-29-19.33.58.101000000000
    3 2013-05-29-19.33.59.328000000000
    4 2013-05-29-19.34.00.845000000000
    5 2013-05-29-19.34.04.722000000000

    9 record(s) selected.

    It deletes the first row. Now let's say we have everything the same:

    db2 => select * from t1

    COL1 COL2
    ----------- --------------------------------
    5 2013-05-29-19.34.02.236000000000
    5 2013-05-29-19.34.03.098000000000
    5 2013-05-29-19.34.03.796000000000
    5 2013-05-29-19.34.04.035000000000
    5 2013-05-29-19.34.04.219000000000
    5 2013-05-29-19.34.04.722000000000

    6 record(s) selected.

    db2 => delete from t1 where col1 in (select col1 from t1 order by col2 fetch fir
    st 1 row only)
    DB20000I The SQL command completed successfully.
    db2 => select * from t1

    COL1 COL2
    ----------- --------------------------------

    0 record(s) selected.

    Why does it delete everything? Shouldn't we still just delete the first row since it is ordered by timestamp? I am a bit confused.

    Thanks

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    you say : delete from t1 where col1 in 5
    all rows qualify
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by proph3t View Post
    Hi,

    I was just curious about this functionality. If I do:

    COL1 COL2
    ----------- --------------------------------
    5 2013-05-29-19.34.02.236000000000
    5 2013-05-29-19.34.03.098000000000
    5 2013-05-29-19.34.03.796000000000
    5 2013-05-29-19.34.04.035000000000
    5 2013-05-29-19.34.04.219000000000
    1 2013-05-29-19.33.56.369000000000
    2 2013-05-29-19.33.58.101000000000
    3 2013-05-29-19.33.59.328000000000
    4 2013-05-29-19.34.00.845000000000
    5 2013-05-29-19.34.04.722000000000

    10 record(s) selected.

    db2 => delete from t1 where col1 in (select col1 from t1 order by col2 fetch fir
    st 1 row only)
    DB20000I The SQL command completed successfully.
    db2 => select * from t1

    COL1 COL2
    ----------- --------------------------------
    5 2013-05-29-19.34.02.236000000000
    5 2013-05-29-19.34.03.098000000000
    5 2013-05-29-19.34.03.796000000000
    5 2013-05-29-19.34.04.035000000000
    5 2013-05-29-19.34.04.219000000000
    2 2013-05-29-19.33.58.101000000000
    3 2013-05-29-19.33.59.328000000000
    4 2013-05-29-19.34.00.845000000000
    5 2013-05-29-19.34.04.722000000000

    9 record(s) selected.

    It deletes the first row. Now let's say we have everything the same:

    db2 => select * from t1

    COL1 COL2
    ----------- --------------------------------
    5 2013-05-29-19.34.02.236000000000
    5 2013-05-29-19.34.03.098000000000
    5 2013-05-29-19.34.03.796000000000
    5 2013-05-29-19.34.04.035000000000
    5 2013-05-29-19.34.04.219000000000
    5 2013-05-29-19.34.04.722000000000

    6 record(s) selected.

    db2 => delete from t1 where col1 in (select col1 from t1 order by col2 fetch fir
    st 1 row only)
    DB20000I The SQL command completed successfully.
    db2 => select * from t1

    COL1 COL2
    ----------- --------------------------------

    0 record(s) selected.

    Why does it delete everything? Shouldn't we still just delete the first row since it is ordered by timestamp? I am a bit confused.

    Thanks
    To delete the oldest row for each col1 you can try something like (untested):

    Code:
    delete from (
         select col1, col2
         from (
             select col1, col2, row_number() over (partition by col1
                                                                    order by col2) as rn
             from t1
         ) where rn = 1
    )
    If you want to delete the oldest row regardless of col1, remove the partion clause
    Last edited by lelle12; 05-30-13 at 06:50.
    --
    Lennart

Posting Permissions

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