Home » SQL & PL/SQL » SQL & PL/SQL » Mass update of column for all rows
Mass update of column for all rows [message #252384] Wed, 18 July 2007 14:12 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
Hi,
I have a table with one of the columns containing a numbered sequence.
For eg
Resource_number
------------
1
2
3
4
5
6
7

Now for eg if a new row is inserted in middle then I assign a temporary number which is the same number as the previous one
For eg.
Resource_number
------------
1
2
3
4
4 <== Newly Inserted row
5
6
7

Now I want to resequence it in SQL, not PLSQL. How to do a mass update of a column. so that now the values are
Resource_number
----------------
1
2
3
4
5
6
7
8

Regards,
Re: Mass update of column for all rows [message #252386 is a reply to message #252384] Wed, 18 July 2007 14:16 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
ROWS inside a table have NO inherent order.
Re: Mass update of column for all rows [message #252387 is a reply to message #252386] Wed, 18 July 2007 14:18 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Supposing If a numbered column has same value say 3 rows with values 1, then with the ORDER BY clause on this column, the value which is inserted first is displayed first and so on.
Re: Mass update of column for all rows [message #252392 is a reply to message #252384] Wed, 18 July 2007 14:29 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
I have a basket of colored balls.
Three of the balls are red.
Which 1 of the three red balls is first?
Which 1 of the three red balls is last?
Re: Mass update of column for all rows [message #252396 is a reply to message #252384] Wed, 18 July 2007 14:36 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
create table test1 (a number, b varchar2(10);

insert into test1 values (1,'Red');
insert into test1 values (2,'Green');
insert into test1 values (3,'Yellow');

select * from test1 order by a;

A B
---- ----------
1 Red
2 Green
3 Yellow

---Now Insert a new row in Middle.

insert into test1 values (2,'Red');
select * from test1 order by a;

A B
------ ----------
1 Red
2 Green
2 Red <== New row inserted comes after 1st 2
3 Yellow


Re: Mass update of column for all rows [message #252399 is a reply to message #252384] Wed, 18 July 2007 14:41 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Pure coincidence.

When you do this query again it could just as easily come out the opposite.

Your best bet is probably to add a time_stamp field and order by that as well.




Re: Mass update of column for all rows [message #252400 is a reply to message #252396] Wed, 18 July 2007 14:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How to order "2 Red" and "2 Green"?
Is this "2 Red" and "3 Green" or "3 Red" and "2 Green"?
Of course, Oracle does not which one was inserted first or last.
So which order?
Or it does not matter?

Regards
Michel
Re: Mass update of column for all rows [message #252401 is a reply to message #252384] Wed, 18 July 2007 14:44 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You NEVER answer my question.
I have a basket of colored balls.
Three of the balls are red.
Which 1 of the three red balls is first?
Re: Mass update of column for all rows [message #252404 is a reply to message #252399] Wed, 18 July 2007 14:48 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Yes,that is true. I ran the same query in 10g and the results were different.
So in that case. what would be the best way to

a) Insert a new row in the table somewhere in the middle.
b) Resequence it in the correct order.


Regards,
Re: Mass update of column for all rows [message #252405 is a reply to message #252384] Wed, 18 July 2007 14:49 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You are gun decking results!
create table test1 (a number, b varchar2(10);

insert into test1 values (1,'Red');
insert into test1 values (2,'Green');
insert into test1 values (3,'Yellow');

select * from test1 order by a;

A B
---- ----------
1 Red
2 Green
3 Yellow

The above code fails with missing closing parenthesis on CREATE TABLE line!

You're On Your On (YOYO)!
Re: Mass update of column for all rows [message #252406 is a reply to message #252384] Wed, 18 July 2007 14:51 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Please NOTE the resultant order!
SQL> create table test1 (a number, b varchar2(10));

Table created.

SQL> 
SQL> insert into test1 values (1,'Red');

1 row created.

SQL> insert into test1 values (2,'Green');

1 row created.

SQL> insert into test1 values (3,'Yellow');

1 row created.

SQL> 
SQL> commit;

Commit complete.

SQL> delete from test1 where a = 2;

1 row deleted.

SQL> insert into test1 values (2,'Red');

1 row created.

SQL> insert into test1 values (2,'Green');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test1 order by a;

         A B
---------- ----------
         1 Red
         2 Green
         2 Red
         3 Yellow

SQL> 

Re: Mass update of column for all rows [message #252407 is a reply to message #252401] Wed, 18 July 2007 14:52 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
You are Correct anacedent. I was little blinded by the fact that everytime I ran the query in 9i, it gave me the same order. The moment I ran the same query in 10g the order changed. So there is no way of knowning which red balls came first Smile

So considering the above table structure what is the logical way
to
1) insert rows in the middle
2) reorder/resequence it.

Regards,
Re: Mass update of column for all rows [message #252411 is a reply to message #252407] Wed, 18 July 2007 15:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still don't answer to my question.

Regards
Michel
Re: Mass update of column for all rows [message #252413 is a reply to message #252411] Wed, 18 July 2007 15:04 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Yes Micheal,

The order does matter the latest row inserted in middle can either be before or after the current row.
So is timstamp then the logical way forward.
Re: Mass update of column for all rows [message #252415 is a reply to message #252413] Wed, 18 July 2007 15:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you have to add a timestamp to your rows as Oracle does not know which one is first.

Regards
Michel
Re: Mass update of column for all rows [message #252417 is a reply to message #252415] Wed, 18 July 2007 15:17 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Now with the timestamp or date column for eg, if the rows look like this

A B C
-------------------------
1 Red 07-18-07
2 Green 07-18-07
2 Red 07-19-07
3 Yellow 07-18-07

What would the sql to resequence it. so that new values are

A B C
-------------------------
1 Red 07-18-07
2 Green 07-18-07
3 Red 07-19-07
4 Yellow 07-18-07
Re: Mass update of column for all rows [message #252418 is a reply to message #252384] Wed, 18 July 2007 15:20 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
The other question is why do you even want to "insert into the middle" of a list. Simply use a sequence and sort the selects in any order that you want on any column you wish. Rewritting an internal sequence is just not a good idea. What happens if two people insert and update at the same time, your sequence numbers get totally messed up. Us a static sequence number and display them in any order you wish.
Re: Mass update of column for all rows [message #252419 is a reply to message #252417] Wed, 18 July 2007 15:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now it's easy just use row_number function to get your new A column.

Regards
Michel
Re: Mass update of column for all rows [message #252420 is a reply to message #252419] Wed, 18 July 2007 15:24 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Now the sequence is there for a purpose because there is a application that needs to fetch the rows in the specific order. So addition of new rows in the middle will not part of a daily job, but as a maintainence once, so it may happen once in a year or may not happen at all.
Re: Mass update of column for all rows [message #252422 is a reply to message #252420] Wed, 18 July 2007 15:25 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Michel,
Can you provide a sample SQL please?
Re: Mass update of column for all rows [message #252424 is a reply to message #252422] Wed, 18 July 2007 15:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just read page on row_number in SQL Reference.

Regards
Michel
Re: Mass update of column for all rows [message #252426 is a reply to message #252415] Wed, 18 July 2007 15:33 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
You're trying to assume order where there is no order. This is a relational database. There is no concept of "inserting a record in the middle". The "middle" as you've defined it simply means that you want to extract some information and order it in a specific way.

There are many techniques that are used to create an order of the information retrieved.

In your example,
Resource_number
------------
1
2
3
4
4 <== Newly Inserted row
5
6
7


you're assuming that the database sticks the record into a specific slot. Well, the reality is that the records can be stuck in wherever there is space. In actuality, your data might be in the database table in any random order. It's up to you to determine what order you want for the data and design a query to extract it in that order. NEVER ASSUME THAT THE DATABASE WILL AUTOMATICALLY ORDER THE DATA FOR YOU!!!

If you want all the "4" records together, you need to use the ORDER BY qualifier

...
order by resource_number


If you want all the "4" records in some specific order, you will need to specify another column in your ORDER BY statement.

Personally, I usually define a unique ID number for each record I enter into a table by using an Oracle SEQUENCE. That assigns a unique number in descending order for every record I enter. Thus, I could use an ORDER BY clause like...

...
order by resource_number, unique_id


This will put all my "4" records together and make sure that they are displayed in the order I entered them.

But, regardless of all this, you MUST understand that the records in a database table do NOT have an inherent order to them that you can consistently depend on.

HTH,
Ron
Re: Mass update of column for all rows [message #252428 is a reply to message #252424] Wed, 18 July 2007 15:45 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Ok, I tried this..I am failing to set the Update STATEMENT .

alter table test1 add d number;
update test1 set d=2222;

select * from test1 order by a;

A B C D
-- ---------- ------------------------------ ----------
1 Red 18-JUL-07 03.37.53.000000 PM 2222
2 Green 18-JUL-07 03.37.53.000000 PM 2222
2 Red 19-JUL-07 03.38.18.000000 PM 2222
3 Yellow 18-JUL-07 03.37.53.000000 PM 2222

select a,b,row_number() over (partition by d order by a,d) new1
from test1;

A B NEWA
----- ---------- ----------
1 Red 1
2 Green 2
2 Red 3
3 Yellow 4

Now How to set this in a update statement..

Re: Mass update of column for all rows [message #252505 is a reply to message #252428] Thu, 19 July 2007 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
One way is:
update test1 t1
set a=(select new_a 
       from ( select rowid row_id, 
                     row_number() over (partition by d order by a,d) new_a
              from test1
             ) b
       where b.row_id = a.rowid)
/

Regards
Michel
Re: Mass update of column for all rows [message #252676 is a reply to message #252505] Thu, 19 July 2007 10:57 Go to previous message
yerics
Messages: 89
Registered: August 2006
Member
Thanks Michel..
Previous Topic: dual table
Next Topic: ORA-00904: "ISNULL":invalid Identifier
Goto Forum:
  


Current Time: Thu Dec 08 02:29:29 CST 2016

Total time taken to generate the page: 0.07178 seconds