Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    manila
    Posts
    21

    Unanswered: Query to retain last 2 updates

    Hi can anyone give me their 2 cents in this query. I'm am to delete some rows on my table and i am supposed to retain only the last 2 updates (timestamp) for every employee number. However i found out that for every 1 employee number, there are as much as 20k rows of updates and when i count the timestamp, there were several records having the same timestamp.

    here's my query to get the last 2 updates which i would eventually convert into a delete statement :

    select empno, last_update
    from table a
    where 2 > (select count(*)
    where a.last_update < last_update)


    however, i got this excerpt from the SQL reference about DELETE statement that says:
    "If the search condition contains a subquery, the subquery can be thought of as being executed each time the search condition is applied to a row, and the results used in applying the search condition. In actuality, a subquery with no correlated references is executed just once, whereas it is possible that a subquery with a correlated reference must be executed once for each row.
    Let T2 denote the object table of a DELETE statement and let T1 denote a table that is referred to in the FROM clause of a subquery of that statement. T1 must not be a table that can be affected by the DELETE on T2. Thus, the following rules apply:
     T1 and T2 must not be the same table.

    If this is the case, then I say that the delete statement will not work. I cant think of another queryt that would eventually DELETE all the other rows except the last 2 updates. Can anyone tell me if there's another way or the only way, I could do this is to create a COBOL program?

    Your help is very much appreciated. FYI. i am using DB2 7 for OS/390

  2. #2
    Join Date
    Apr 2003
    Posts
    191

    Re: Query to retain last 2 updates

    Hi starra,

    I don't see a problem with a query like

    delete from table a where 2 > ( select count(*) where a.last_update < last_update )

    If it doesn't work, you should file a PMR with IBM support. From my point of view, it is perfectly valid SQL.

    Johann

    Originally posted by starra
    Hi can anyone give me their 2 cents in this query. I'm am to delete some rows on my table and i am supposed to retain only the last 2 updates (timestamp) for every employee number. However i found out that for every 1 employee number, there are as much as 20k rows of updates and when i count the timestamp, there were several records having the same timestamp.

    here's my query to get the last 2 updates which i would eventually convert into a delete statement :

    select empno, last_update
    from table a
    where 2 > (select count(*)
    where a.last_update < last_update)


    however, i got this excerpt from the SQL reference about DELETE statement that says:
    "If the search condition contains a subquery, the subquery can be thought of as being executed each time the search condition is applied to a row, and the results used in applying the search condition. In actuality, a subquery with no correlated references is executed just once, whereas it is possible that a subquery with a correlated reference must be executed once for each row.
    Let T2 denote the object table of a DELETE statement and let T1 denote a table that is referred to in the FROM clause of a subquery of that statement. T1 must not be a table that can be affected by the DELETE on T2. Thus, the following rules apply:
     T1 and T2 must not be the same table.

    If this is the case, then I say that the delete statement will not work. I cant think of another queryt that would eventually DELETE all the other rows except the last 2 updates. Can anyone tell me if there's another way or the only way, I could do this is to create a COBOL program?

    Your help is very much appreciated. FYI. i am using DB2 7 for OS/390

  3. #3
    Join Date
    Sep 2003
    Location
    manila
    Posts
    21
    hi Johann,

    Thanks for your reply. the query should work, i did try it on other database and it works perfectly, however, when i ran it against my data, it did not return any rows. upon looking at the data, i saw that there are several rows with exactly the same timestamp. and right now that is my dilemma.

  4. #4
    Join Date
    Apr 2003
    Posts
    191
    Hi starra,

    well, if you can prove it, do not hesitate and file a complaint with IBM support.

    Johann

    Originally posted by starra
    hi Johann,

    Thanks for your reply. the query should work, i did try it on other database and it works perfectly, however, when i ran it against my data, it did not return any rows. upon looking at the data, i saw that there are several rows with exactly the same timestamp. and right now that is my dilemma.

Posting Permissions

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