Home » SQL & PL/SQL » SQL & PL/SQL » Copy/Ammend/Insert Row based on ID
Copy/Ammend/Insert Row based on ID [message #432674] Wed, 25 November 2009 11:44 Go to next message
chris-uk-lad
Messages: 11
Registered: November 2009
Junior Member
Hi all

Im new to both the forum and PL SQL so forgive me.

Im looking to making a script that will ammend some already existing values within a table, based upon specified IDs

Something like:

For REFID('ad101', 'ad102', 'ad103')
Do
*copy row*
*paste row with ammendment to the copied value*
Loop
End For


Ive been looking at cursors which i believe are like recordsets, dont believe i can work with this as im specifying the search criteria instead of using existing data in a table.

Id really appreciate some links which could help me find the answer myself or examples. Ive tried looking around but, being new to PLSQL, dont fully know what im looking for.
Re: Copy/Ammend/Insert Row based on ID [message #432675 is a reply to message #432674] Wed, 25 November 2009 11:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not just a single UPDATE?

Regards
Michel
Re: Copy/Ammend/Insert Row based on ID [message #432676 is a reply to message #432674] Wed, 25 November 2009 11:51 Go to previous messageGo to next message
cookiemonster
Messages: 12420
Registered: September 2008
Location: Rainy Manchester
Senior Member
Suspect you just want to use an update statement.
Hard to tell with the lack of information and I wouldn't be able to point you at anything more usefull than the documentation.
If you give more detail on what you're trying to do we can probably give you a better answer.
Re: Copy/Ammend/Insert Row based on ID [message #432677 is a reply to message #432674] Wed, 25 November 2009 11:52 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Im new to both the forum and PL SQL so forgive me.
It appears you found & tried to comply with Posting Guidelines.
THANKS!
Perhaps rather than starting with PL/SQL, you should become proficient with SQL.

Oracle manuals can be found at
http://tahiti.oracle.com

Many fine coding example can be found at
http://asktom.oracle.com

Good Luck!
Re: Copy/Ammend/Insert Row based on ID [message #432678 is a reply to message #432674] Wed, 25 November 2009 12:11 Go to previous messageGo to next message
chris-uk-lad
Messages: 11
Registered: November 2009
Junior Member
Im able to use both SQL and VBA so a fairly ok standard, im just confused on the method to use preselected ID's, theres gonna be maybe 100 rows to update and id rather avoid creating 100 update rows based on this criteria hence trying to find a quicker method, incase it needs doing in the future also.
Re: Copy/Ammend/Insert Row based on ID [message #432679 is a reply to message #432674] Wed, 25 November 2009 12:12 Go to previous messageGo to next message
cookiemonster
Messages: 12420
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well if we knew where these preselected IDs were coming from it might help.
Re: Copy/Ammend/Insert Row based on ID [message #432680 is a reply to message #432678] Wed, 25 November 2009 12:16 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Copy/Ammend/Insert Row based on ID [message #432681 is a reply to message #432676] Wed, 25 November 2009 12:17 Go to previous messageGo to next message
chris-uk-lad
Messages: 11
Registered: November 2009
Junior Member
cookiemonster wrote on Wed, 25 November 2009 11:51
Suspect you just want to use an update statement.
Hard to tell with the lack of information and I wouldn't be able to point you at anything more usefull than the. If you give more detail on what you're trying to do we can probably give you a better answer.


To explain better if i can, im trying to look through records in a table, and if they exit im making a copy of the row, ammending a reference number within it, then inserting it into the table. If they dont exist (if statement) then i will just insert the record fresh without the ammendment. As i said in my newest post, there is maybe over 100 records to check for and i would like to avoid 100 update statements to conserve time, easier to just paste in the id and run the script.

Any more details plase ask, im just confused how to search based on that criteria without referring to another table (most of my SQL has been via VBA in Access).

For a post i just noticed, the IDs are from a CSV document, they may or may not exist in the system. Im awake of the where REFID in ('AD101','AD102',.. etc) but i couldnt get it to work in a similar pattern to a recordset.

Maybe im confusing myself, as i said im pretty new on this

Many Thanks

[Updated on: Wed, 25 November 2009 12:19]

Report message to a moderator

Re: Copy/Ammend/Insert Row based on ID [message #432686 is a reply to message #432678] Wed, 25 November 2009 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use something like the following (using OO4O, check syntax it has been long I used VB):
for I=1 to 100
  OraParameter("ID").value = <Ith ID>
  OraParameter("NEWVAL").value = <Ith new value>
  OraDatabase.ExecuteSql("update mytable set columnToUpdate=:NEWVAL where id=:ID")
next I

Oracle send only once the statement with all the values, this is the equivalent of the FORALL PL/SQL statement.

Or you can use 2 arrays of parameters:
Dim Ids as OraParamArray 
Dim NewVal as OraParamArray
Set Ids = OraDatabase.Parameters("ID")
Set NewVal = OraDatabase.Parameters("NEWVAL")
For I = 1 to 100
  Ids(i) = <Ith ID>
  NewVal(i) = <Ith new value>
next I
OraDatabase.ExecuteSQL ("update mytable set columnToUpdate=:NEWVAL where id=:ID")

Regards
Michel
Re: Copy/Ammend/Insert Row based on ID [message #432687 is a reply to message #432686] Wed, 25 November 2009 12:55 Go to previous messageGo to next message
chris-uk-lad
Messages: 11
Registered: November 2009
Junior Member
Michel Cadot wrote on Wed, 25 November 2009 12:49
You can use something like the following (using OO4O, check syntax it has been long I used VB):
for I=1 to 100
  OraParameter("ID").value = <Ith ID>
  OraParameter("NEWVAL").value = <Ith new value>
  OraDatabase.ExecuteSql("update mytable set columnToUpdate=:NEWVAL where id=:ID")
next I

Oracle send only once the statement with all the values, this is the equivalent of the FORALL PL/SQL statement.

Or you can use 2 arrays of parameters:
Dim Ids as OraParamArray 
Dim NewVal as OraParamArray
Set Ids = OraDatabase.Parameters("ID")
Set NewVal = OraDatabase.Parameters("NEWVAL")
For I = 1 to 100
  Ids(i) = <Ith ID>
  NewVal(i) = <Ith new value>
next I
OraDatabase.ExecuteSQL ("update mytable set columnToUpdate=:NEWVAL where id=:ID")

Regards
Michel


I can get my head around that, shall give it a try.

Does anyone know of a method to store the row as a string but segmented so each can be called without allocating as variable using mids hence allowing me to ammend(if they even work in plsql)? Everything i read about copying a row just indicates copying to a new table, not in the current one so i guess thats out of the picture for an easy resolution.

[Updated on: Wed, 25 November 2009 12:57]

Report message to a moderator

Re: Copy/Ammend/Insert Row based on ID [message #432688 is a reply to message #432687] Wed, 25 November 2009 13:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Everything i read about copying a row just indicates copying to a new table, not in the current one so i guess thats out of the picture for an easy resolution.

Copying a row with new values in the same table is updating a row.
I think you have try to fit your requirements in the wrong way. You must use RDBMS and SQL features and not try to do it with procedural language.

Regards
Michel

[Updated on: Wed, 25 November 2009 13:22]

Report message to a moderator

Re: Copy/Ammend/Insert Row based on ID [message #432746 is a reply to message #432688] Thu, 26 November 2009 00:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Update <> copy
To copy a row with (some) new values, use insert as select.

for example:
insert into my_table
   ( id
   , col1
   , col2
   )
   (select id
    ,      :newval_for_col1
    ,      col2
    from   my_table
    where  id in (:list_of_ids)
   )
Re: Copy/Ammend/Insert Row based on ID [message #432749 is a reply to message #432746] Thu, 26 November 2009 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
JUst to precise, if :var means a bind variable, "in (:list_of_ids)" will not work.

Regards
Michel
Re: Copy/Ammend/Insert Row based on ID [message #432752 is a reply to message #432749] Thu, 26 November 2009 01:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It was just an idea, not a full blown working piece of code (maybe I should have added that).
I don't know about VBA; I know that for some java ORM tools you can write these kind of queries and the List of id's you provide will be rewritten.
Re: Copy/Ammend/Insert Row based on ID [message #432754 is a reply to message #432752] Thu, 26 November 2009 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I posted code for VB (it is the same for update or insert), VBA is a subset of VB, I don't know if the code I posted will work with VBA.

Regards
Michel
Re: Copy/Ammend/Insert Row based on ID [message #432791 is a reply to message #432674] Thu, 26 November 2009 05:45 Go to previous messageGo to next message
chris-uk-lad
Messages: 11
Registered: November 2009
Junior Member
Hi guys,

To provide insight, ive attatched some sample code ive created, including my solution to the copy issue using %ROWTYPE.

Where this code uses 1 reference id, i wish it to use, for example, 100 reference id's so i can just copy the list of ID's i have from access into this script and run once instead of individually. Is it possibly to use a FOR loop, somehow using the list of REF_ID's as a recordset without them existing in a table (pasted in from access)? This would also include a list of USER_STATUS values that corresponds to each user. (I know im inserting instead of updating, there is a reason for this)

DECLARE
    R_TAB1           TABLE1%ROWTYPE;
    
    CUR_DATE         STRING(10);
    REF_ID	     STRING(7);
    USER_STATUS      STRING(3);
    
BEGIN
    REF_ID := 'AD101';
    USER_STATUS := 'STAGE 2';

    SELECT *
        INTO R_TAB1
        FROM TABLE1 WHERE REF_ID = USER_REF AND STATUS = USER_STATUS;

    SELECT (TO_CHAR(SYSDATE,'DD/MM/YYYY')) INTO CUR_DATE FROM DUAL;
      
    INSERT INTO TABLE1 (GROUP, STATUS, REF_ID, START_DATE)
 VALUES (R_AUTH.GROUP, USER_STATUS, R_AUTH.REF_ID, CUR_DATE);
          
END;

[Updated on: Thu, 26 November 2009 05:55] by Moderator

Report message to a moderator

Re: Copy/Ammend/Insert Row based on ID [message #432793 is a reply to message #432674] Thu, 26 November 2009 05:53 Go to previous messageGo to next message
cookiemonster
Messages: 12420
Registered: September 2008
Location: Rainy Manchester
Senior Member
Think you want a variable in list

Some other notes:
1) You don't need to select sysdate from dual, you can reference it directly.
2) If cur_date is a date then you don't need to to_char sysdate, if it's not a date you should make it a date.
3) This can probably be done as a single insert select, using your example:
INSERT INTO TABLE1 (GROUP, STATUS, REF_ID, START_DATE)
(SELECT GROUP, USER_STATUS, REF_ID, sysdate)
FROM TABLE1
WHERE REF_ID = USER_REF AND STATUS = USER_STATUS;
Re: Copy/Ammend/Insert Row based on ID [message #432795 is a reply to message #432793] Thu, 26 November 2009 05:58 Go to previous message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
cookiemonster wrote on Thu, 26 November 2009 12:53
Think you want a variable in list

Some other notes:
1) You don't need to select sysdate from dual, you can reference it directly.
2) If cur_date is a date then you don't need to to_char sysdate, if it's not a date you should make it a date.
3) This can probably be done as a single insert select, using your example:
INSERT INTO TABLE1 (GROUP, STATUS, REF_ID, START_DATE)
(SELECT GROUP, USER_STATUS, REF_ID, sysdate)
FROM TABLE1
WHERE REF_ID = USER_REF AND STATUS = USER_STATUS;

And merge this INSERT statement in the VB code I provided you and you're done.

Regards
Michel

Previous Topic: How to chech whether roles got changed to an user
Next Topic: to_char(interval)
Goto Forum:
  


Current Time: Thu Dec 08 06:25:26 CST 2016

Total time taken to generate the page: 0.13049 seconds