CODEWITHSUNDEEP
sqloracle-databaseoracle10g
zephyrus
zephyrus

Reputation: 261

How to update a column dynamically with the current value of another column?

I'm looking to write an update statement something like below:

update table set
comments = NVL (null, acknowledgement_status),
acknowledgement_status = 'Acknowledge',
alert_updated_time = sysdate,
acknowledged_by = 'Allen'
where alert_id = 8;

Actually, the values need to be updated from a JSP page. If a user fails to give a comment, then the corresponding acknowledgement_status that the user gives should be updated as comments. But from the above query, the previous acknowledgement_status is being set as comments. How to go about this?

Consider the table content as below:

    Alert_ID   Acknowledgement_status   Comments   Alert_updated_time   Acknowledged_by
    --------   ----------------------   --------   ------------------   ---------------
8               OPEN                    None                              AUTO

Now above is the table content. The JSP has comments field a text box and acknowledgement_status as drop down. When the user changes the Acknowlegement_status with comments as blank, I want the acknowledgement status to be updated as comment. i.e:

update table set
comments = NVL (textbox.value, acknowledgement_status),
acknowledgement_status = dropdown.value,
alert_updated_time = sysdate,
acknowledged_by = sessionid.value;
where alert_id = 8;

when textbox.value = null, dropdown.value = 'Acknowledge', sessionid.value = 'Allen' the table gets updated as below:

  Alert_ID   Acknowledgement_status   Comments   Alert_updated_time   Acknowledged_by
  --------   ----------------------   --------   ------------------   ---------------
    8            Acknowledge            OPEN          sysdate                 Allen

but what I want is:

  Alert_ID   Acknowledgement_status    Comments       Alert_updated_time   Acknowledged_by
  --------   ----------------------    --------       ------------------   ---------------
    8            Acknowledge           Acknowledge           sysdate                 Allen

I can rather write,

update table set
comments = NVL (textbox.value, dropdown.value),
acknowledgement_status = dropdown.value,
alert_updated_time = sysdate,
acknowledged_by = sessionid.value;
where alert_id = 8;

But again, I have plans to decode based on dropdown.value and I thought it would be easier if it was possible to update with the current value.

Help appreciated.

Upvotes: 0

Views: 1495

Answers (4)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

Here is one way, if you want to pass the values only once:

UPDATE tableX t
SET 
  (comments, acknowledgement_status, alert_updated_time, acknowledged_by)
=
  ( SELECT 
      COALESCE(com, ack_st), ack_st, sd, ack_by
    FROM
    ( SELECT 
        textbox.value    AS com,
        dropdown.value   AS ack_st,
        sysdate          AS sd,
        sessionid.value  AS ack_by
      FROM dual
    ) d
  )              
WHERE t.alert_id = 8 ;

Tested in SQL-Fiddle

Upvotes: 2

Jacob
Jacob

Reputation: 14741

Try the following

 update table set comments  =   
 case when (comments  is null) then acknowledgement_status else  comments   end,
 acknowledgement_status = 'Acknowledge',
 alert_updated_time = sysdate,
 acknowledged_by = 'Allen'
 where alert_id = 8;

Trigger approach

CREATE OR REPLACE TRIGGER test
    BEFORE UPDATE
    ON table     FOR EACH ROW
DECLARE

begin

    if (:new.comments is null) then

    :new.comments := :new.acknowledgement_status;

    end if;

END;
/

Upvotes: 1

Devolus
Devolus

Reputation: 22104

update table set
comments = decode(comment, null, 'Acknowledge', comment),
acknowledgement_status = 'Acknowledge',
alert_updated_time = sysdate,
acknowledged_by = 'Allen'
where alert_id = 8;

This will update the comment field to Acknowledge if the value is null. Not sure about that "prevoius" thing you mentioned. If you need something else, then you should update your question with a clearer description.

Since you want to updated according to other states as well, you can stack the decode because it essentially acts like an if...then..else

Upvotes: 0

chetan
chetan

Reputation: 2896

UPDATE table
SET comments = COALESCE(comment, acknowledgement_status),
    acknowledgement_status = 'Acknowledge',
    alert_updated_time = SYSDATE,
    acknowledged_by = 'Allen'
WHERE alert_id = 8;

Upvotes: 0

Related Questions