Re: Insert/Update entire rows?

From: Patrick Flahan <flahan_at_gte.net>
Date: Thu, 29 Jul 1999 16:19:19 GMT
Message-ID: <aa%n3.6$QU4.2368_at_paloalto-snr1.gtei.net>


As far as I know there isn't a built-in to duplicate a row but with some changes. My take on the problem is that you will need to handle this progrmatically. You can retrieve all the column values and then do an update or insert depending on your needs.

I included a very rough piece of code below. I don't know if it will help, but it may give you some ideas. Where I work we did something kind of like this. We built standard procedures to modify data, so that business rules could be enforced and so that historical data can be maintained properly.

Hope this helps,
Patrick Flahan

PROCEDURE UpdateOrInsert(precEmp IN emp%type) IS

-- emp is a table so precEmp would be record of type Emp
-- that would have all columns populated from a record in the
-- emp table.
--
--TABLE: EMP
--emp_id NUMBER
--last_name  VARCHAR2(35)
--first_name  VARCHAR2(35)
--ssn              VARCHAR2(11)

BEGIN
  IF (some_condition) --do an update
  THEN

     UPDATE emp
             SET .....
      WHERE .....;
  ELSE  --or use an elsif to test for some other condition
     INSERT INTO emp
       (emp_id
       ,last_name
       ,first_name
       ,ssn)
      VALUES
       (precEmp.emp_id
       ,precEmp.last_name
       ,precEmp.first_name
       ,precEmp.ssn);

   END IF; END UpateOrInsert

Ken Halsted <kenman_at_mail.snider.net> wrote in message news:rq0lde$0$37nspbj$n2p_at_corp.supernews.com...
> I was wondering if someone can tell me if it's possible to insert/update
an
> entire row using PL/SQL if all I want to change is one column which is
part
> of the key. I basically want a duplicated row with a different key. Is
> there an easy way to do this without having to specify every column?
>
> One more time:
> I am reading a row by using a cursor. I want to change a key columns
value.
> I then want to insert/update into the exact same table all values except
one
> key column which will make it unique.
>
> Thanks for any help at all.
>
> KenMan.
>
> --
> _________________
> Kenneth W. Halsted
> Mountaire Corporation
> ph: (501) 399-8812
> url: http://www.mountaire.com
> email: kenman_at_mail.snider.net
>
>
>
Received on Thu Jul 29 1999 - 18:19:19 CEST

Original text of this message