Professional Web Applications Themes

How to limit the number of rows in delete statement? - IBM DB2

Hallo! I have the following situation: db2 v7.2 on windows one table without primary key and without unique index some indentical rows. how can i delete only one row which matches a where condition? that means i search for something like: delete from tab where row in (select * from tab where col=value fetch only 1 row) In version 8 the following statement works: delete from tab where col1 in (select col1 from tab where col1=value fetch only 1 row) but in version 7 i get an error SQL0104N. how can this be done in version 7.2 for windows? how ...

  1. #1

    Default How to limit the number of rows in delete statement?

    Hallo!

    I have the following situation:

    db2 v7.2 on windows

    one table without primary key and without unique index

    some indentical rows.

    how can i delete only one row which matches a where condition?
    that means i search for something like:
    delete from tab where row in (select * from tab where col=value fetch
    only 1 row)

    In version 8 the following statement works:
    delete from tab where col1 in (select col1 from tab where col1=value
    fetch only 1 row)
    but in version 7 i get an error SQL0104N.

    how can this be done in version 7.2 for windows?

    how can i avoid the need to select a specific column, e.g. in a table
    with 15 columns i won't specify each column!?

    is there some way to use a "rowid"?

    thanks
    andreas




    alederer Guest

  2. #2

    Default Re: How to limit the number of rows in delete statement?

    alederer schrieb: 

    My solution: Create a table temp with same structure.
    insert into temp select distinct * from source
    delete from source
    create primary key or unique index
    insert into source select * from temp
    drop table temp

    Regards,
    --
    Burkhard Schultheis
    Tele Data Electronic, Wagnerstr. 10, D-76448 Durmersheim
    Email: de
    Phone: +49-7245-9287-21, Fax: +49-7245-9287-30
    Burkhard Guest

  3. #3

    Default Re: How to limit the number of rows in delete statement?

    Hi Andreas,

    This is exactly why we shortly come up with a new feature in V8:

    delete from (select * from tab where col1=value fetch first 1 row only);

    Note that the examples you provided don't work, they still delete all
    rows where col1=value.

    Something with row_number() (not tested):

    create view vtab(r,col1) as select row_number() over (order by
    col1),col1 from tab;

    delete from vtab v where col1=value and r = (select min(r) from vtab
    where col1=v.col1);

    Andreas

    alederer wrote:
     

    Andreas Guest

  4. #4

    Default Re: How to limit the number of rows in delete statement?

    use row_number()
    AK Guest

  5. #5

    Default Re: How to limit the number of rows in delete statement?

    You need v8 to use fetch first in a subselect.

    alederer wrote:
     

    Blair Guest

  6. #6

    Default Re: How to limit the number of rows in delete statement?

    For example:

    delete FROM (SELECT c1, row_number() OVER
    (ORDER BY c1)
    AS rn FROM t1)
    AS tr WHERE rn BETWEEN 1 and 1

    AK wrote:
     

    Blair Guest

  7. #7

    Default Re: How to limit the number of rows in delete statement?

    Blair Adamache <> wrote 
    I think that "DELETE FROM (full-select)" is new from DB2 UDB V8.1.4.
    In V7, for example:
    DELETE FROM tab
    WHERE <primary_key>
    IN (SELECT <primary_key>
    FROM (SELECT <primary_key>
    , ROWNUMBER() OVER() AS rownum
    FROM tab
    WHERE col1 = value) AS t
    WHERE rownum = 1)
    Tokunaga Guest

  8. #8

    Default Re: How to limit the number of rows in delete statement?

    Burkhard Schultheis wrote:
     
     
    >
    > My solution: Create a table temp with same structure.
    > insert into temp select distinct * from source
    > delete from source
    > create primary key or unique index
    > insert into source select * from temp
    > drop table temp[/ref]

    as i wrote above, i only want to delete one row which matches the
    condition.
    i think your code deletes all duplicates.

    because i have no influence to the application running on that table i
    don't know how this application is handling duplicate rows, maybe it
    needs duplicate rows!?

    thanks
    andreas

    alederer Guest

  9. #9

    Default Re: How to limit the number of rows in delete statement?

    I'm sorry. I forgot you wrote no primary key nor unique index.
    My example can't be used.
    If some columns of mathing rows are different, you can use list of
    columns instead of <primary key>.
    But, if all columns are identical within matching rows, I have no idea
    other than that of Burkhard and Andreas.
    Tokunaga Guest

  10. #10

    Default Re: How to limit the number of rows in delete statement?

    Tokunaga T. wrote:
     
    >
    > I think that "DELETE FROM (full-select)" is new from DB2 UDB V8.1.4.
    > In V7, for example:
    > DELETE FROM tab
    > WHERE <primary_key>
    > IN (SELECT <primary_key>
    > FROM (SELECT <primary_key>
    > , ROWNUMBER() OVER() AS rownum
    > FROM tab
    > WHERE col1 = value) AS t
    > WHERE rownum = 1)[/ref]

    as i wrote, the problem is, that the table has no primary keys. in this
    case your code deletes all rows with the same values.

    e.g. you have a table (very simple only for example)
    create table t(a integer);
    insert into a values(5);
    insert into a values(5);
    insert into a values(5);
    insert into a values(3);
    insert into a values(3);
    insert into a values(3);

    if you execute your statement
    delete from t where a in (select a from
    (select a , rownumber()over() as rownum
    from t
    where a = 3) as tt
    where rownum=1)

    there are no rows in t where a=3

    what i want after executing the statement is
    select * from t

    A
    -----------
    5
    5
    5
    3
    3

    that means i will delete only "one" row where a=3 !

    thanks
    andreas

    Andreas Guest

  11. #11

    Default Re: How to limit the number of rows in delete statement?

    i think we wrote our answers at the same time :-)

    andreas

    Andreas Guest

  12. #12

    Default Re: How to limit the number of rows in delete statement?

    I think your real problem is that you have a table without any primary key.
    Maybe you need to rethink your business processes, or at least your system
    design, so that the things being deleted *are* unique and can therefore have
    a primary key. That should simplify your life in the long run.

    After all, what do two identical rows in the same table actually mean? If
    there is no way to distinguish them, I can't see any point in keeping more
    than one of the same row.

    Sorry, I don't mean to be dogmatic. I know that it is occasionally necessary
    to do unusual things to cope with special situations. I'm just suggesting
    that you consider whether it really is essential to have duplicate rows in
    your table.

    Rhino

    "alederer" <at> wrote in message
    news:3f7ae418$inode.at... 


    Rhino Guest

  13. Moderated Post

    Default Re: How to limit the number of rows in delete statement?

    Removed by Administrator
    AK Guest
    Moderated Post

  14. #14

    Default Re: How to limit the number of rows in delete statement?

    Rhino wrote:
     

    you are right, a database design which uses tables without primary keys
    and with duplicate rows is bad.

    but i have no influecne about the apllication tables.
    we write an application independent utility and have to handle this
    situations.

    my solution is to open a cursor, go to the first row and execute a
    update or delete statement with the "where current of cursorname" extension

    thanks
    andreas

    alederer Guest

  15. #15

    Default Re: How to limit the number of rows in delete statement?

    Hmm, I encourage to keep an open mind on this one. Sometimes even with a
    primary key I may still not care which row I delete.
    An example for the usage of a multiset would be a bike-rental place.
    When I want a blue bike I'm not asking for one with a specific serial
    number, any blue bike will do.
    So simply delete one blue bike form the inventory list.
    Tell me which one it is and I'll take it and bring it back in a couble of
    hours :-)

    When we implemented "delete from select" we had lots of discussions on what
    it means for it to interact with "fetch first n rows"......

    Similar funny stuff happens with update.
    When my department drives to lunch we know we need n cars. We don't care who
    goes where, as long as everyone finds a spot.
    SELECT owner, seats_available INTO :carpoolwith, :seatsleft
    FROM NEW TABLE(UPDATE (SELECT seats_available
    FROM cars WHERE
    seats_available > 0
    FETCH FIRST ROW ONLY)
    AS car
    SET seats_available =
    seats_available -1);

    For more fun with these you need to come to Vegas and attend "SQL Unleashed"
    *wink*

    Cheers
    Serge



    Serge Guest

  16. #16

    Default Re: How to limit the number of rows in delete statement?

    We have to be careful about holy wars. For example, many ERP
    applications choose not to use Referential Integrity (RI) in the rdbms -
    rather, they enforce it at the application level. This applies to
    some/all SAP R3 releases and to some Peoplesoft apps as well.

    Naive users will often dump such data into a data warehouse for mining.
    If they import or LOAD the same data twice, we now have two versions
    of each row. In an ideal world, the tables into which the ERP data was
    loaded would be predefined with RI or at least primary keys - but if the
    users start with the DDL from the ERP apps, they may not have as many
    constraints identified to the RDBMS as are enforced at the ERP app
    level. What are the solutions to this?

    1. Use Informational Constraints (new in DB2 v8) - these tell the
    optimizer that RI exists, but it's not enforced by DB2, since the
    application can (we hope) be trusted to ensure the RI

    2. Create a primary key with ALTER TABLE (or add a unique constraint -
    worst case, if the key column(s) is/are nullable, create a unique index
    over them, as it will tolerate one null row in the key.

    3. If you end up with duplicate rows, you can use coalesce to get rid of
    them - other ways of doing are described in this excellent FAQ:
    http://www.harddiskcafe.de/db2faq/index.html

    Serge Rielau wrote: 

    Blair Guest

  17. #17

    Default Re: How to limit the number of rows in delete statement?

    Blair,
    thanks for the link
     

    there is one statement I did not completely agree with:

    FETCH FIRST <n> ROWS can be also expressed like this:
    "SELECT <cols> FROM (SELECT <cols>, ROWNUMBER()
    OVER(ORDER BY <ord>) as rn) AS ot WHERE rn <= <n>"

    statements like this are often implemented as
    - generate the whole result set
    - filter it

    the difference is significant if the table is big enough.

    What do you think?
    AK Guest

  18. #18

    Default Re: How to limit the number of rows in delete statement?

    You're right. If you really want to right queries likt this, you're
    better to define te table with an IDENTITY column. The example below is
    for situations where the table is already defined, and adding an
    IDENTITY column is not possible.

    AK wrote:
     
    >
    >
    > there is one statement I did not completely agree with:
    >
    > FETCH FIRST <n> ROWS can be also expressed like this:
    > "SELECT <cols> FROM (SELECT <cols>, ROWNUMBER()
    > OVER(ORDER BY <ord>) as rn) AS ot WHERE rn <= <n>"
    >
    > statements like this are often implemented as
    > - generate the whole result set
    > - filter it
    >
    > the difference is significant if the table is big enough.
    >
    > What do you think?[/ref]

    Blair Guest

  19. #19

    Default Re: How to limit the number of rows in delete statement?

    Hi Serge,
     

    I've encoutered several similar problems. I t=hink usually it's much
    much faster to get rid of cursors. like this:

    It is necessary to assign rooms to guests, so that every guest gets
    one room, if available

    CREATE TABLE ROOM(
    ROOM_ID INT NOT NULL PRIMARY KEY,
    SMOKING CHAR(1) NOT NULL CHECK(SMOKING IN ('N','Y')));

    CREATE TABLE GUEST(
    GUEST_ID INT NOT NULL PRIMARY KEY,
    SMOKER CHAR(1) NOT NULL CHECK(SMOKER IN ('N','Y')));

    Using an OLAP function ROW_NUMBER(), there is no need to open two
    cursors and iterate through rooms and guests. Yo understand how it
    works, let us first have a look at this select query and its output:
    SELECT ROOM_NUMBER, GUEST_NUMBER, ROOM_ID, GUEST_ID
    FROM
    (SELECT ROW_NUMBER() OVER(ORDER BY ROOM_ID) AS ROOM_NUMBER, ROOM_ID
    FROM ROOM) AS R
    JOIN
    (SELECT ROW_NUMBER() OVER(ORDER BY GUEST_ID) AS GUEST_NUMBER, GUEST_ID
    FROM GUEST) AS G
    ON ROOM_NUMBER=GUEST_NUMBER


    what do you think?
    AK Guest

  20. #20

    Default Re: How to limit the number of rows in delete statement?

    Nice one, I'd yank the ORDER BY's though.
    There is no need to match small guest(ids) with small room (numbers) :-)
    I'm wondering if that's faster than two cursors driven by a stored
    procedure.
    The rownumber() function gnertates a sorted resultset with unique property
    on the column.
    So I suppose a sort-merge-join should do the job efficiently.
    Would be an interesting experiment to see whether the optimizer figures that
    one out :-)

    Cheers
    Serge


    Serge Guest

Page 1 of 2 12 LastLast

Similar Threads

  1. how to limit number of rows in a textarea
    By ryna in forum Macromedia Dynamic HTML
    Replies: 2
    Last Post: January 4th, 06:35 PM
  2. How to limit # of rows returned from query
    By Steve Grosz in forum Macromedia ColdFusion
    Replies: 7
    Last Post: May 2nd, 07:35 PM
  3. Replies: 3
    Last Post: September 5th, 04:16 PM
  4. need help figuring out how to delete rows?
    By Deadsam in forum PHP Development
    Replies: 3
    Last Post: August 25th, 06:03 AM
  5. Insert 100 rows in just one statement
    By David Portas in forum Microsoft SQL / MS SQL Server
    Replies: 6
    Last Post: July 1st, 12:52 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139