1

Topic: select in updete to interpose rownum

Colleagues, good afternoon!
I have such data set, for example:

select a from t where id in (25,26,27) order by id asc;

Result and, for example:
3
7
11
I need to make update fields "and" on rownum (i.e. field values "and" became in sequence):
1
2
3
Such variant does not roll:

update (select rownum, a from t where id in (25,26,27) order by id asc) set a = rownum;

Help, !

2

Re: select in updete to interpose rownum

iv_roman_vl wrote:

Such variant does not roll:

update (select rownum, a from t where id in (25,26,27) order by id asc) set a = rownum;

RTFM MERGE

3

Re: select in updete to interpose rownum

MERGE INTO t
USING (select rownum as x, id from t where id in (25,26,27) order by id asc) z
ON (t.id = z.id)
WHEN MATCHED
THEN UPDATE
SET t.a = z.x

4

Re: select in updete to interpose rownum

Dshedoo;

USING (select row_number () OVER (order by id asc) as x, id from t where id in (25,26,27)) z

5

Re: select in updete to interpose rownum

Dshedoo;
Thanks!!! Works!!!

6

Re: select in updete to interpose rownum

All thanks for involvement!
Applied the above-stated methods and all works!
But it is not pleasant  decisions a little.
The task sounds so:
Books lie on regiments. Each book on a regiment has a place (place number).
Books can move from a shelf on a shelf, either to the beginning of a shelf or in the end.
At relocation of books in the shelf beginning, number of a place of moved books begins with 1. Number of a place of already available books on a regiment begins with an amount of the added books on a shelf. Thus numbers of books from a shelf whence moved, is enumerated with 1.
At relocation of books in the end of a shelf, number of books available on a regiment remains former, and numbers of books moved on a shelf begins with an amount of books present on a regiment. Thus numbers of books from a shelf whence moved, is enumerated with 1.
For example:

Id | NomerPolki | NomerKnigiNaPolke
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 2 3

At relocation of books 4,5 from a shelf 2 on a shelf 1 in the shelf beginning :

Id | NomerPolki | NomerKnigiNaPolke
1 1 3
2 1 4
3 1 5
4 1 1
5 1 2
6 2 1

At relocation of books 4,5 from a shelf 2 on a shelf 1 in the shelf end :

Id | NomerPolki | NomerKnigiNaPolke
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
6 2 1

I implemented it so, I will begin

From relocation of books in the shelf end

:
In the beginning I find an amount of books on that shelf on which I want to move

select count (id) into count from Books where the Shelf = 1;

Then, I move books on a shelf:

MERGE INTO Books
USING (select rownum as x, id from Books where ID in (4,5) order by id asc) z
ON (Knigi.IDKnigi = z.id)
WHEN MATCHED
THEN UPDATE
SET Books.  = z.x + count, Books.  = 1;

here me arranges.
And now,

From relocation of books in the shelf beginning

:
I renumber places of books on a regiment whence we take away books:

MERGE INTO Books
USING (select rownum as x, id from Books where the Shelf = 2 and ID not in (4,5) order by id asc) z
ON (Knigi.IDKnigi = z. ID)
WHEN MATCHED
THEN UPDATE
SET Books.  = z.x

Further, I find an amount of moved books:

select count (id) into count from Books where id in (4,5);

Further, I Renumber places of books on a regiment on which I want to deliver books since an amount of moved books:

MERGE INTO Books
USING (select rownum as x, id from Books where the Shelf = 1 order by id asc) z
ON (Knigi.IDKnigi = z. ID)
WHEN MATCHED
THEN UPDATE
SET Books.  = z.x + count;

And in the end, we move books on a shelf 1:

MERGE INTO Books
USING (select rownum as x, id from Books where id in (4,5) order by id asc) z
ON (Knigi.IDKnigi = z. ID)
WHEN MATCHED
THEN UPDATE
SET Books.  = z.x, Books.  = 1;

Me does not arrange  at relocation in the shelf beginning.
Whether it is possible as that it to write down one request?

7

Re: select in updete to interpose rownum

iv_roman_vl;
1. It is not necessary to use titles of variables same as well as names of commands, and to read difficult, and problems can arise (into count)
2. Did not find recomputation of a shelf with which removed books for "insertions in the beginning"... If the shelf remains the book not sorted on it will return difficult
3. For an insertion in the beginning, I would interpose records with NomerKnigiNaPolke = 0 and ran once renumbering with sorting order by NomerKnigiNaPolke, id

8

Re: select in updete to interpose rownum

In procedure it is necessary to transfer ID books and their new regiments (new_polka) and places (new_mesto).
1) From the cursor we select the book.
2) It is pushed in old_polka and old_mesto current position of the book.
3) Apdejtim all books on new_polke, at which mesto> = new_mesto. set mesto = mesto+1
4) Apdejtim all books on old_polke, at which mesto> old_mesto. set mesto = mesto - 1.
5) Apdejtim the book set mesto = new_mesto, polka = new_polka

9

Re: select in updete to interpose rownum

Dshedoo wrote:

In procedure it is necessary to transfer ID books and their new regiments (new_polka) and places (new_mesto).
1) From the cursor we select the book.
2) It is pushed in old_polka and old_mesto current position of the book.
3) Apdejtim all books on new_polke, at which mesto> = new_mesto. set mesto = mesto+1
4) Apdejtim all books on old_polke, at which mesto> old_mesto. set mesto = mesto - 1.
5) Apdejtim the book set mesto = new_mesto, polka = new_polka

To fulfill points in such sequence: 1 - 2 - 3 - 5 - 4

10

Re: select in updete to interpose rownum

Dshedoo wrote:

In procedure it is necessary to transfer ID books and their new regiments (new_polka) and places (new_mesto).
1) From the cursor we select the book.
2) It is pushed in old_polka and old_mesto current position of the book.
3) Apdejtim all books on new_polke, at which mesto> = new_mesto. set mesto = mesto+1
4) Apdejtim all books on old_polke, at which mesto> old_mesto. set mesto = mesto - 1.
5) Apdejtim the book set mesto = new_mesto, polka = new_polka

I so understand that if at us three books actions 2,3,4,5 will be are moved repeat on three times.
This variant well approaches if at us the book can, for example from shelf center will be moved to other center of a shelf.
At me such case cannot be.
At me the book strictly, either in the end or in the beginning. And the old shelf as sequence is simply enumerated. That is books on an old shelf simply "shifted" to each other.
And the offered variant will be superfluous as it will be necessary to repeat points 2,3,4,5 for each book.
All the same thanks for involvement.
I understood that my method takes place to be.
Simply me interested, whether I can two requests (1st recalculation from an amount of moved books, 2 - recalculation of moved books) at relocation in a head  in one. Probably is not present. But all the same all thanks.
PS: about a name into count I know. Thanks!

11

Re: select in updete to interpose rownum

iv_roman_vl wrote:

Simply me interested, whether I can two requests (1st recalculation from an amount of moved books, 2 - recalculation of moved books) at relocation in a head  in one. Probably is not present.

Well from what at once is not present.
It is possible to write 1  which it is based on sorting of NomerKnigiNaPolke and then ID on transmittable value of NomerPolki.
And the insertion will be  NomerPolki on necessary, in the beginning changes NomerKnigiNaPolke = 0 for interposed books
In the end changes NomerKnigiNaPolke translates in null
After it is launched our unique  which enumerates NomerKnigiNaPolke for old and then and for a new shelf.

12

Re: select in updete to interpose rownum

iv_roman_vl;
Relocation in "a single-user" mode?
ps
You want merge to remove one books from a shelf and to suppose on another with renumberings?
Or the operator two - one  books from a shelf, another puts with renumberings?
.....
stax

13

Re: select in updete to interpose rownum

Stax wrote:

iv_roman_vl;
Relocation in "a single-user" mode?
ps
You want merge to remove one books from a shelf and to suppose on another with renumberings?
Or the operator two - one  books from a shelf, another puts with renumberings?
.....
stax

Yes, in the single-user.
Yes, one merge.

14

Re: select in updete to interpose rownum

iv_roman_vl;
Books act in film from one shelf, or can  at once from several?
All removed books are put on one specific shelf?
.....
stax

15

Re: select in updete to interpose rownum

Stax wrote:

iv_roman_vl;
Books act in film from one shelf, or can  at once from several?
All removed books are put on one specific shelf?
.....
stax

For one operation (one pushing the button to move)
Books act in film only from one shelf. Also are put only on one shelf (strictly or in the shelf beginning, or in the end).
Whether also a question in that it is possible to make transfer of books from one shelf on another, with appropriate recalculation on both regiments;
At an insertion in the beginning of a shelf,
One request.

16

Re: select in updete to interpose rownum

iv_roman_vl;

/*drop table books;
create table books (id number (10), rack number (10), numOfRack number (10));
insert into books (id, rack, numOfRack) values (1,1,1);
insert into books (id, rack, numOfRack) values (2,1,2);
insert into books (id, rack, numOfRack) values (3,1,3);
insert into books (id, rack, numOfRack) values (4,2,1);
insert into books (id, rack, numOfRack) values (5,2,2);
insert into books (id, rack, numOfRack) values (6,2,3);
*/
select row_number () over (partition by case when id in (1,2) then 2/*new rack*/else rack end
order by
case when id in (1,2) then 2/*new rack*/else rack end,
case when id in (1,2) then decode (2/*1 - at first*/, 1,0) else numOfRack end;
id) new_numOfRack,
id, case when id in (1,2) then 2/*new rack*/else rack end new_rack
from books
--where rack in (1,2)
order by new_numOfRack

17

Re: select in updete to interpose rownum

It I transport books 1 and 2 from a shelf 1 on a shelf 2  two shelves (well their only two smile)))
Where:

decode (2/*1 - at first*/, 1,0)

- 1 - from the beginning, 2 for example from the end

2/*new rack*/

- a shelf on which transferred

18

Re: select in updete to interpose rownum

iv_roman_vl wrote:

it is passed...
For one operation (one pushing the button to move)
Books act in film only from one shelf. Also are put only on one shelf (strictly or in the shelf beginning, or in the end).
Whether also a question in that it is possible to make transfer of books from one shelf on another, with appropriate recalculation on both regiments;
At an insertion in the beginning of a shelf,
One request.

And so "one button" you can cause procedure which will do everything that I described earlier.
More correctly to implement not that approach which solves a specific target, and what will be general-purpose, and will be, including to solve a specific target.

19

Re: select in updete to interpose rownum

iv_roman_vl;

SQL> select * from t order by p, n;
ID P N
---------- ---------- ----------
1 1 1
2 1 2
3 1 3
4 2 1
5 2 2
6 2 3
7 2 4
7 rows selected.
SQL> print:t_in
T_IN
----------
1
SQL> MERGE INTO t
2 USING (
3 select
4 k.*
5, row_number () over (partition by p order by srt, n) rn
6 from
7 (select id, p, n, 1 srt from t where p =:t_in - old books
8 union all
9 select id:t_in, n, 0 srt from t where id in (4,5) - added (0 in the beginning 2 in the end)
10 union all
11 select id, p, n, 1 srt from t where id not in (4,5)
12 and p = (select min (p) from t where id in (4,5)) - without 
13) k
14) z
15 ON (t. ID = z. ID)
16 WHEN MATCHED
17 THEN UPDATE
18 SET t.p=z.p, n=z.rn
19 /
7 rows merged.
SQL> select * from t order by p, n;
ID P N
---------- ---------- ----------
4 1 1
5 1 2
1 1 3
2 1 4
3 1 5
6 2 1
7 2 2
7 rows selected.
SQL> rollback;
Rollback complete.
SQL> ed
Wrote file afiedt.buf
1 MERGE INTO t
2 USING (
3 select
4 k.*
5, row_number () over (partition by p order by srt, n) rn
6 from
7 (select id, p, n, 1 srt from t where p =:t_in - old books
8 union all
9 select id:t_in, n, 2 srt from t where id in (4,5) - added (0 in the beginning 2 in the end)
10 union all
11 select id, p, n, 1 srt from t where id not in (4,5)
12 and p = (select min (p) from t where id in (4,5)) - without 
13) k
14) z
15 ON (t. ID = z. ID)
16 WHEN MATCHED
17 THEN UPDATE
18* SET t.p=z.p, n=z.rn
SQL> /
7 rows merged.
SQL> select * from t order by p, n;
ID P N
---------- ---------- ----------
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
6 2 1
7 2 2
7 rows selected.
SQL> rollback;
Rollback complete.
SQL>

....
stax

20

Re: select in updete to interpose rownum

Stax;
Thanks, this that that are necessary!!!!!!!!!!!!!!!!!!
Script FIRE!!!
HUGE RESPECT!!!
I feel as I  was pumped over!!!!

21

Re: select in updete to interpose rownum

iv_roman_vl;
It is possible to make and is more optimal (without/less union all), the sampling condition will be
Did  that it was more clear
ps
Removal of books from several shelves simultaneously
p in (select p from t where id in (4,5))
.....
stax

22

Re: select in updete to interpose rownum

Stax;
Yes, thanks! The principle understood.
While from several shelves it is not required.
But I will add to the arsenal.