Simple UPDATE - should be easy but just can't get it! [message #681020] |
Sun, 07 June 2020 12:57 |
|
dragam
Messages: 31 Registered: May 2012 Location: Ireland
|
Member |
|
|
Hi all,
I have what should be a relatively simple UPDATE but it just simply won't work for me!
I've been Googling and pfaffing about for almost two hours and I'm going insane!
I have a table so (a fiddle for all this is available here):
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=86e3d891cc73815363cfc263195606c2
CREATE TABLE t
(
a_id NUMBER not null,
t_id NUMBER,
c_name VARCHAR2(100),
d_order NUMBER default 0,
CONSTRAINT t_pk PRIMARY KEY (a_id, t_id)
);
And data:
INSERT INTO t
WITH cte1 (a_id, t_id, c_name, d_order) AS
(
SELECT 675150, 770650, 'name', 0 FROM dual UNION
SELECT 675150, 780700, 'name', 0 FROM dual UNION
SELECT 675150, 780701, 'name', 0 FROM dual UNION
SELECT 675180, 770550, 'name', 0 FROM dual UNION
SELECT 675180, 780800, 'name', 0 FROM dual UNION
SELECT 675180, 780801, 'name', 0 FROM dual
)
SELECT * FROM cte1;
This all works fine - see fiddle.
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=86e3d891cc73815363cfc263195606c2
What I want to do is to UPDATE the table and put in a d_order sorted by a_id and t_id,
(i.e. via this SQL):
SELECT
t.a_id,
t.t_id,
t.d_order,
ROW_NUMBER() OVER (PARTITION BY t.a_id
ORDER BY t.a_id, t.t_id DESC) AS rn
FROM t;
Result is -
A_ID T_ID D_ORDER RN
675150 780701 0 1
675150 780700 0 2
675150 770650 0 3
675180 780801 0 1
675180 780800 0 2
675180 770550 0 3
So, (very very simple) - I want to UPDATE t so that d_order = rn!
This would be easy in other servers (Oracle isn't my wheelhouse), so I'd appreciate any help!
For bonus marks - alternative methods of doing this (via MERGE?) or others would be great
so that I could start to get a grip on Oracle's syntax and "mindset"!
TIA and rgs,
Pól...
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=86e3d891cc73815363cfc263195606c2
[Updated on: Sun, 07 June 2020 13:45] Report message to a moderator
|
|
|
|
|
|
|
|
|