Home » SQL & PL/SQL » SQL & PL/SQL » UPDATE Query Help
UPDATE Query Help [message #250372] Mon, 09 July 2007 17:11 Go to next message
Wilco
Messages: 1
Registered: July 2007
Location: Tacoma, WA
Junior Member
So I've got a table with some data. Due to a bug in our software, some of the data was inserted incorrectly, and I'm hoping to correct the problem manually for the data that is already there. Here's a simplified version of what is there currently:

C_NAME | C_GROUP | C_VAL1 | C_VAL2 | C_VAL3 | C_VAL4
-----------------------------------------------------
Item01 | 0       | 12.4   | 15.3   | 0.0    | 0.0
Item01 | 1       | 11.4   | 0.0    | 0.0    | 0.0
Item01 | 2       | 13.9   | 0.0    | 0.0    | 0.0
Item02 | 0       | 12.4   | 15.3   | 0.0    | 0.0
Item02 | 1       | 11.4   | 0.0    | 0.0    | 0.0
Item02 | 2       | 13.9   | 0.0    | 0.0    | 0.0


In this simplified case, Item01 should only be on one row (with the values from C_VAL1 in the extra rows moved to C_VAL3 and C_VAL4 in the first row). The same would then apply for Item02.

My biggest problem is I'm not entirely sure how to loop through this to accomplish what I want.

This is what I have so far:

UPDATE table
SET C_VAL3 = 
  (SELECT C_VAL1 FROM table 
    WHERE C_NAME='Item01' 
      AND C_GROUP=1)
WHERE C_NAME='Item01'
  AND C_GROUP=0;


Now imagine this example is really a table with a huge list of values and extra groups. Is there a way I can turn this into some sort of loop that will walk through each ItemXX and apply the same type of update?

The desired result would look something like this (compare with the first illustration):
C_NAME | C_GROUP | C_VAL1 | C_VAL2 | C_VAL3 | C_VAL4
-----------------------------------------------------
Item01 | 0       | 12.4   | 15.3   | 11.4   | 13.9
Item02 | 0       | 12.4   | 15.3   | 11.4   | 13.9

[Updated on: Mon, 09 July 2007 17:14]

Report message to a moderator

Re: UPDATE Query Help [message #250373 is a reply to message #250372] Mon, 09 July 2007 17:43 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
I am certain that 1 or more contributors can problem 1 VERY elegant SQL statement that will solve your problem.
I am more of a simple & straight forward type of programmer.
You could write SQL to write SQL where each statement would fix 1 column.
In fact for my $DAY_JOB I am doing just that fix data in one Production table that is incorrect due to an application bug.
Re: UPDATE Query Help [message #250374 is a reply to message #250372] Mon, 09 July 2007 18:40 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You did not provide any create and insert scripts, as per the posting guidelines in the sticky at the top of the forum, so the following is untested and I assume your table name is not really "table":

UPDATE table t1
SET t1.C_VAL3 = 
  (SELECT t3.C_VAL1 FROM table t3 
    WHERE t3.C_NAME=t1.CNAME 
      AND C_GROUP=1),
    t1.C_VAL4 = 
  (SELECT t4.C_VAL1 FROM table t4 
    WHERE t4.C_NAME=t1.CNAME 
      AND C_GROUP=2)
WHERE C_GROUP=0;

DELETE FROM table
WHERE C_GROUP IN (1,2);

Re: UPDATE Query Help [message #250491 is a reply to message #250372] Tue, 10 July 2007 04:32 Go to previous messageGo to next message
boogle
Messages: 1
Registered: July 2007
Junior Member
you could try this pl/sql code:
declare
   cursor fix_cur is
   select * from fix
   where col2 = 0;
   temp fix_cur%rowtype;
begin
   open fix_cur;
   loop
      fetch fix_cur into temp;
      update fix
      set col5 = (select col3 from fix where col1 = temp.col1 and col2 = 1),
          col6 = (select col3 from fix where col1 = temp.col1 and col2 = 2)
      where col1 = temp.col1 and col2 = 0;
      
      
   exit when fix_cur%notfound;
   end loop;
   delete from fix
   where col2 in (1,2);
end;
Re: UPDATE Query Help [message #250507 is a reply to message #250491] Tue, 10 July 2007 05:43 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why using PL/SQL when you can do it in SQL.
At least, you should lock the rows you want to update using a "select for update".

Regards
Michel
Previous Topic: XMLTYPE Column
Next Topic: Guidence
Goto Forum:
  


Current Time: Wed Dec 07 22:28:39 CST 2016

Total time taken to generate the page: 0.10439 seconds