Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    20

    Unanswered: Update only latest record

    Hi,

    I have this table:

    Code:
    create table status_reading (
    	id int not null generated always as identity,
    	reading_time timestamp not null with default current timestamp,
    	status varchar(8) not null,
    	primary key (id)
    );
    
    insert into status_reading (status) values ('OK');
    insert into status_reading (status) values ('ERROR2');
    insert into status_reading (status) values ('ERROR5');
    insert into status_reading (status) values ('OK');
    insert into status_reading (status) values ('ERROR2');
    insert into status_reading (status) values ('ERROR3');
    insert into status_reading (status) values ('ERROR2');
    insert into status_reading (status) values ('ERROR5');
    insert into status_reading (status) values ('ERROR3');
    insert into status_reading (status) values ('OK');
    
    select * from status_reading;
    
    ID          READING_TIME               STATUS  
    ----------- -------------------------- --------
              1 2013-02-08-15.19.53.261612 OK      
              2 2013-02-08-15.20.05.020475 ERROR2  
              3 2013-02-08-15.20.12.818210 ERROR5  
              4 2013-02-08-15.20.21.496451 OK      
              5 2013-02-08-15.20.25.411419 ERROR2  
              6 2013-02-08-15.20.28.485604 ERROR3  
              7 2013-02-08-15.20.33.792696 ERROR2  
              8 2013-02-08-15.20.40.172258 ERROR5  
              9 2013-02-08-15.20.43.460787 ERROR3  
             10 2013-02-08-15.20.47.988834 OK      
    
      10 record(s) selected.
    How do I update only the latest record that has status ERROR3?
    Meaning only the record with ID = 9?

    I have tried the query below, and it works, but I'm just wondering if
    there is a more elegant solution.

    Code:
    update status_reading set reading_time = current timestamp, 
    status = 'ERROR2' where status = 'ERROR3' and id = 
    (select max(id) from status_reading where status = 'ERROR3');
    Using some OLAP function would probably also work,
    but it would also make the query much longer.

    DB2 LUW 9.7 on Ubuntu 12.10.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That is about the best you can do. You really do not need this clause: "where status = 'ERROR3'" since the ID column is unique and you are using that also.

    Andy

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

    I have this table:

    Code:
    create table status_reading (
    	id int not null generated always as identity,
    	reading_time timestamp not null with default current timestamp,
    	status varchar(8) not null,
    	primary key (id)
    );
    
    insert into status_reading (status) values ('OK');
    insert into status_reading (status) values ('ERROR2');
    insert into status_reading (status) values ('ERROR5');
    insert into status_reading (status) values ('OK');
    insert into status_reading (status) values ('ERROR2');
    insert into status_reading (status) values ('ERROR3');
    insert into status_reading (status) values ('ERROR2');
    insert into status_reading (status) values ('ERROR5');
    insert into status_reading (status) values ('ERROR3');
    insert into status_reading (status) values ('OK');
    
    select * from status_reading;
    
    ID          READING_TIME               STATUS  
    ----------- -------------------------- --------
              1 2013-02-08-15.19.53.261612 OK      
              2 2013-02-08-15.20.05.020475 ERROR2  
              3 2013-02-08-15.20.12.818210 ERROR5  
              4 2013-02-08-15.20.21.496451 OK      
              5 2013-02-08-15.20.25.411419 ERROR2  
              6 2013-02-08-15.20.28.485604 ERROR3  
              7 2013-02-08-15.20.33.792696 ERROR2  
              8 2013-02-08-15.20.40.172258 ERROR5  
              9 2013-02-08-15.20.43.460787 ERROR3  
             10 2013-02-08-15.20.47.988834 OK      
    
      10 record(s) selected.
    How do I update only the latest record that has status ERROR3?
    Meaning only the record with ID = 9?

    I have tried the query below, and it works, but I'm just wondering if
    there is a more elegant solution.

    Code:
    update status_reading set reading_time = current timestamp, 
    status = 'ERROR2' where status = 'ERROR3' and id = 
    (select max(id) from status_reading where status = 'ERROR3');
    Using some OLAP function would probably also work,
    but it would also make the query much longer.

    DB2 LUW 9.7 on Ubuntu 12.10.
    Elegance and length is in the eye of the beholder, is this in the ball park?

    Code:
    update (
        select id, reading_time, status
                , row_number()  over (order by reading_time desc) as rn 
        from status_reading 
        where status = 'ERROR3'
    ) set reading_time = current timestamp
         , status = 'ERROR2' 
    where rn = 1
    I assumed latest referred to reading_time (noticed then that you used id)
    --
    Lennart

  4. #4
    Join Date
    Jan 2012
    Posts
    20
    Thank you 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
  •