I need to be able to Select rows of data from an Oracle db. I then need to be able to ammend certain parts of the data selected and insert it back in the same table. Any ideas?

Posted on 2008-11-04
Last Modified: 2013-12-19
I need to be able to Select data from an Orcale table and use it as a template. In the db there is multiple records stored for one 'template', I need to copy most fields associated with the certain 'template'. Once the data has been retrieved I then need to change the data already stored in some fields i.e  change project No to the actual project number that has been inputted on the GUI. Im presuming I need some sort of array/dataset and will need a loop, unfortunatly Im stuck with how. Help much appreciated!

Select statment

SELECT ProjectNo, TaskNo, CustomerNo, etc etc FROM P_AMaster WHERE ProjectNo like '" + ddlTemplates.SelectedItem.Text + "'";

Ive tried to give an example of the table
ProjectNo TaskNo CustomerNo PlannerNo ...... ...... .......

Temp1     0000   .......     .......

Temp1     1111   .......    .......

Temp1     2222   ......     .......

Open in new window

Question by:dwezil
    1 Comment
    LVL 29

    Accepted Solution


    You may need to write a stored procedure, something like this:

       p_template_id     VARCHAR2,
       p_new_projectno   VARCHAR2,
       p_customerno      NUMBER
       FOR t IN (SELECT projectno, taskno, customerno, etc1, etc2
                   FROM p_amaster
                  WHERE projectno = p_template_id)
          INSERT INTO p_amaster
               VALUES (p_new_projectno, t.taskno, p_customerno, t.etc1, t.etc2);
       END LOOP;

    Open in new window


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now