Results 1 to 8 of 8
  1. #1
    Join Date
    May 2009
    Posts
    4

    Post Unanswered: stored proc to update only those fields which user updates and not all

    Hi,

    I have a doubt in update sp. I want to update only those fields which user updates and not all. How to do this???

    Please help.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    User updates several columns that belong to a table.

    Now you'd also want to update same columns, right? If so, why? Where's the purpose in doing that? For example, if user sets "column_a = 1", why would you update it to "column_a = 2"?

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Seems to me that you could use a VIEW and an INSTEAD OF trigger to check the :OLD values against the :NEW, and then dynamically build an UPDATE stmt which you execute using EXECUTE IMMEDIATE.

    You could do the same with a PROCEDURE, but then you have to query the underlying table for all of the values, when they're already available as :OLD and :NEW in the trigger.

    --=Chuck

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    WHY EVEN HAVE A VIEW, use a before insert/update trigger and you can do anything you want to the row.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Some people abstract the data model by using procedures, others have a security policy in place which prevents them from allowing direct access to tables.

    In this case though, maybe the OP wants to only have the database perform the DML that ultimately necessary. By the time you get to the row-level trigger, you've already executed a DML statement, and it's too late.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Crystal clear is better

    Quote Originally Posted by sravanpvsr
    Hi,

    I have a doubt in update sp. I want to update only those fields which user updates and not all. How to do this???

    Please help.
    Maybe what we need is a clear statement of your requirements.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I want to update only those fields which user updates and not all.
    >How to do this???
    By having the SET clause contain only fields user changed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Dec 2003
    Posts
    1,074
    This is not tested, but this is how I would start the approach. If you were willing to pu in the work, you could probably automate the build of procedures like this based on views like ALL_TAB_COLS:

    Code:
    create table my_table
    ( pk number(10),
      field2 varchar2(10),
      field3 varchar2(10),
    );
    
    create or replace procedure update_my_table (p_pk number, p_field2 varchar2, p_field3 varchar2)
    is
      l_old_data   my_table%ROWTYPE; 
      l_set_clause varchar2(500);
      l_sql_stmt   varchar2(500);
    begin
    
      select *
      into l_old_data
      from my_table
      where pk = p_pk;
    
      if nvl(p_field1, ' ') != nvl(l_old_data.field1, ' ') then
        l_set_clause := ', field1 = '''||p_field1||'''';
      end if;
    
      if nvl(p_field2, ' ') != nvl(l_old_data.field2, ' ') then
        l_set_clause := ', field2 = '''||p_field2||'''';
      end if;
      
      if nvl(l_set_clause, ' ') != ' ' then
        l_set_clause := substr(l_set_clause, 3);
      end if;
    
      l_sql_stmt :=               'update table my_table ';
      l_sql_stmt := l_sql_stmt || 'set '||l_set_clause;
      l_sql_stmt := l_sql_stmt || 'where pk = '||p_pk;
    
      execute immediate l_sql_stmt;
    
    
    end update_my_table;
    --=Chuck

Posting Permissions

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