Home » SQL & PL/SQL » SQL & PL/SQL » Simple UPDATE - should be easy but just can't get it! (Oracle 18c - but should apply to any version!)
Simple UPDATE - should be easy but just can't get it! [message #681020] Sun, 07 June 2020 12:57 Go to next message
dragam
Messages: 28
Registered: May 2012
Location: Ireland
Junior 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! Smile

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

Re: Simple UPDATE - should be easy but just can't get it! [message #681021 is a reply to message #681020] Sun, 07 June 2020 14:19 Go to previous messageGo to next message
Michel Cadot
Messages: 67367
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from t;
      A_ID       T_ID C_NAME                  D_ORDER
---------- ---------- -------------------- ----------
    675150     770650 name                          0
    675150     780700 name                          0
    675150     780701 name                          0
    675180     770550 name                          0
    675180     780800 name                          0
    675180     780801 name                          0

6 rows selected.

SQL> merge into t t1
  2  using ( SELECT rowid, ROW_NUMBER() OVER (PARTITION BY t.a_id ORDER BY t.t_id DESC) AS rn
  3          FROM t ) t2
  4  on ( t1.rowid = t2.rowid )
  5  when matched then update set d_order = t2.rn
  6  /

6 rows merged.

SQL> select * from t order by t.a_id, t.t_id DESC;
      A_ID       T_ID C_NAME                  D_ORDER
---------- ---------- -------------------- ----------
    675150     780701 name                          1
    675150     780700 name                          2
    675150     770650 name                          3
    675180     780801 name                          1
    675180     780800 name                          2
    675180     770550 name                          3

6 rows selected.
Re: Simple UPDATE - should be easy but just can't get it! [message #681022 is a reply to message #681021] Sun, 07 June 2020 15:16 Go to previous messageGo to next message
dragam
Messages: 28
Registered: May 2012
Location: Ireland
Junior Member

Thanks for your response - much appreciated and I am grateful for your input!

However, is it a standard method? I mean, surely there's an UPDATE statement that will work in this relatively simple scenario?

Pól...

Re: Simple UPDATE - should be easy but just can't get it! [message #681023 is a reply to message #681022] Sun, 07 June 2020 17:17 Go to previous messageGo to next message
Michel Cadot
Messages: 67367
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sure but MERGE will be more efficient.

Re: Simple UPDATE - should be easy but just can't get it! [message #681024 is a reply to message #681023] Mon, 08 June 2020 00:16 Go to previous messageGo to next message
Michel Cadot
Messages: 67367
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For instance:
SQL> rollback;

Rollback complete.

SQL> update t t1
  2  set d_order =
  3      ( select rn
  4        from ( select rowid rid, ROW_NUMBER() OVER (PARTITION BY t.a_id ORDER BY t.t_id DESC) AS rn
  5               from t ) t2
  6        where t2.rid = t1.rowid )
  7  /

6 rows updated.

SQL> select * from t order by t.a_id, t.t_id DESC;
      A_ID       T_ID C_NAME                  D_ORDER
---------- ---------- -------------------- ----------
    675150     780701 name                          1
    675150     780700 name                          2
    675150     770650 name                          3
    675180     780801 name                          1
    675180     780800 name                          2
    675180     770550 name                          3

6 rows selected.
Re: Simple UPDATE - should be easy but just can't get it! [message #681035 is a reply to message #681020] Mon, 08 June 2020 16:02 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2985
Registered: January 2010
Location: Connecticut, USA
Senior Member
Storing cross-row data in a column is almost never a good idea. It is easy to populate first time, but what happens when row is inserted/updated/deleted? Every such change would require serialization.

SY.
Re: Simple UPDATE - should be easy but just can't get it! [message #681072 is a reply to message #681035] Wed, 10 June 2020 13:38 Go to previous message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
agreed. The D_ORDER can be simply generated by using row_number function at time of select
Previous Topic: Creating a not well-formed XML
Next Topic: Multiple rows into on "TEMP" table?
Goto Forum:
  


Current Time: Wed Sep 23 02:48:19 CDT 2020