Solved

PLSQL: update with dense_rank()

Posted on 2014-10-30
24
273 Views
Last Modified: 2014-10-30
hi experts:

i need execute this query
update stage_bd_exports_paino
   set idventaproductocentro = (select dense_rank() over(order by pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa) as idventaproductocentro
                                  from stage_bd_exports_paino pvs
                                 order by pvs.codigocentro,
                                          pvs.codigoCliente,
                                          materialReal,
                                          pvs.organizacionventa)

but i have this error
ORA-00907: missing right parenthesis
0
Comment
Question by:enrique_aeo
  • 8
  • 6
  • 6
  • +1
24 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40414721
I don't think you should do it like that because you need a correlated query, and that won't be efficient with an analytic function like dense_rank().

I believe you will need to join a "derived table" something like this (you need to use the real primary key of that table)

UPDATE stage_bd_exports_paino SBEP
SET SBEP.idventaproductocentro = X.idventaproductocentro
INNER JOIN (
            SELECT
              ID
            , DENSE_RANK() OVER (ORDER BY pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa) AS idventaproductocentro
            FROM stage_bd_exports_paino pvs
            ) X ON SBEP.ID = X.ID

Open in new window

0
 
LVL 24

Expert Comment

by:chaau
ID: 40414722
In oracle you should do it differently. Try this:
update 
 (select idventaproductocentro, pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa, 
dense_rank() over(order by pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa) as idventaproductocentro_new
                                  from stage_bd_exports_paino pvs
) t
SET t.idventaproductocentro = t.idventaproductocentro_new

Open in new window

0
 

Author Comment

by:enrique_aeo
ID: 40414726
i have this error
ORA-00933: SQL command not properly ended
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40414727
sorry that isn't legitimate Oracle syntax
0
 

Author Comment

by:enrique_aeo
ID: 40414730
in this query
update
 (select idventaproductocentro, pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa,
dense_rank() over(order by pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa) as idventaproductocentro_new
                                  from stage_bd_exports_paino pvs
) t
SET t.idventaproductocentro = t.idventaproductocentro_new

ORA-01732: data manipulation operation not legal on this view
0
 
LVL 24

Expert Comment

by:chaau
ID: 40414731
You need to include more columns to the subquery, specifically the columns that are included in the primary key. Can you show us the table structure?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 420 total points
ID: 40414732
When I see updates like this, I instantly think MERGE:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606

As Paul pointed out, you need some 'key' on which to update so there is some where clause.

If you could provide sample data and expected results we can provide better examples.
0
 

Author Comment

by:enrique_aeo
ID: 40414740
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40414744
sorry, my fault entirely. Oracle doesn't support direct joins with an update.

The following is untested, and I have assumed ID is the primary key:

MERGE
INTO    stage_bd_exports_paino SBEP
USING   (
            SELECT
              ID
            , DENSE_RANK() OVER (ORDER BY pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa) AS idventaproductocentro
            FROM stage_bd_exports_paino pvs
        ) x
ON      (SBEP.ID = X.ID)
WHEN MATCHED THEN
UPDATE
SET SBEP.idventaproductocentro = X.idventaproductocentro
;

Open in new window

0
 
LVL 24

Expert Comment

by:chaau
ID: 40414747
The same query with the MERGE:
 MERGE INTO stage_bd_exports_paino
  USING
  (
      select idventaproductocentro, pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa,
dense_rank() over(order by pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa) as idventaproductocentro_new,
ROWID r
from stage_bd_exports_paino pvs )
  ON (ROWID = r)
  WHEN MATCHED THEN UPDATE
  SET idventaproductocentro = idventaproductocentro_new;

Open in new window

0
 

Author Comment

by:enrique_aeo
ID: 40414754
both query with the MERGE
i have this error
ORA-00905: missing keyword
0
 

Author Comment

by:enrique_aeo
ID: 40414759
i have this version

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 24

Expert Comment

by:chaau
ID: 40414763
OK, as you do not have the primary key the only query that would work will be:
update 
 (select rowid, 
IDVENTAPRODUCTOCENTRO ,
ORGANIZACIONVENTA     ,
CODIGOCLIENTE         ,
SPOT_NSPOT            ,
CODIGOCENTRO          ,
DIRECCIONZONAVENTA    ,
MATERIALREAL          ,
IDANIOEJERCICIO       ,
IDVENTAMES            ,
IDMES                 ,
MILLARES              ,
COSTOFIJO             ,
RESINA                ,
PRECIO                ,
TOTAL                 ,
dense_rank() over(order by pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa) as idventaproductocentro_new
                                  from stage_bd_exports_paino pvs
) t
SET t.idventaproductocentro = t.idventaproductocentro_new

Open in new window

The idea is to include all columns and the ROWID to allow Oracle to uniquely identify the record that needs to be updated
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40414764
>>Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production

Going from very old memory but I think that leaves out MERGE.  Back in 9i I remember you had to have both clauses "WHEN MATCHED" and WHEN NOT MATCHED".  One does an update, the other an insert.  I don't remember seeing a trick to get around the INSERT side.
0
 

Author Comment

by:enrique_aeo
ID: 40414769
i have this error:
ORA-01732.png
0
 
LVL 24

Expert Comment

by:chaau
ID: 40414774
There is no way but to create a primary key for your table. Can you tell us the reason why your table is without the primary key?
0
 

Author Comment

by:enrique_aeo
ID: 40414778
the table has more than 5,000 records, tell me how I can create a primary key
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 60 total points
ID: 40414783
ORA-01732:      data manipulation operation not legal on this view
Cause:      An attempt was made to use an UPDATE, INSERT, or DELETE statement on a view that contains expressions or functions or was derived from more than one table. If a join operation was used to create the view or the view contains virtual columns derived from functions or expressions, then the view may only be queried.
Action:      UPDATE, INSERT, or DELETE rows in the base tables instead and restrict the operations on the view to queries.

We need to use the table(s) not a view
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40414784
I still don't know what you are trying to do but your original update, just lose the ORDER BY on the query and it should run.

Not sure if you'll get the results you want, but it should run.

update stage_bd_exports_paino
   set idventaproductocentro = (
	select dense_rank() over(order by pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa) as idventaproductocentro
                                  from stage_bd_exports_paino pvs
	) 
/

Open in new window

0
 
LVL 24

Assisted Solution

by:chaau
chaau earned 20 total points
ID: 40414787
The primary key is what uniquely identifies each of the records in your table. It can consist of a single column (i.e. Order ID) or be a composite key (i.e. Company+OrderID - for the situations where each of the companies have their own order numbering system). Only you should know what these columns are. If there is no such column(s) you can create one (like an ID column proposed by Paul) that will be internal to this table and be populated by a value from a sequence
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40414791
Oh!

IDventaproductocentro

Looks like you are trying to add a primary key to this table

If that is true then please use ROW_NUMBER() not DENSE_RANK()
0
 

Author Comment

by:enrique_aeo
ID: 40414808
i need dense_rank because should be grouped with the same number every 12 records

i create primary key
PK_MITABLA primary key (CODIGOCENTRO, CODIGOCLIENTE, MATERIALREAL, ORGANIZACIONVENTA, IDMES)
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 420 total points
ID: 40414841
Try this:
merge into stage_bd_exports_paino a
using (
	select
		CODIGOCENTRO, CODIGOCLIENTE, MATERIALREAL, ORGANIZACIONVENTA, IDMES,
		dense_rank() over(order by pvs.codigocentro, pvs.codigoCliente, materialReal, pvs.organizacionventa) as idventaproductocentro
        from stage_bd_exports_paino pvs
) mydata
on (
	a.CODIGOCENTRO = mydata.CODIGOCENTRO and
	a.CODIGOCLIENTE = mydata.CODIGOCLIENTE and
	a.MATERIALREAL = mydata.MATERIALREAL and
	a.ORGANIZACIONVENTA = mydata.ORGANIZACIONVENTA and
	a.IDMES = mydata.IDMES
   )
when matched then update
	set idventaproductocentro = mydata.idventaproductocentro
when not matched then insert
	(idventaproductocentro ) values ( null )
/

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40414847
yes... I had second thoughts... lack of faith etc.
If it were me I might do something like that, but then I probably wouldn't be in this position
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Pfile and SPfile - Oracle 2 57
Migrating an SQL 2008 database to Oracle 12c 3 89
Oracle RAC 12c 8 60
Oracle SQL 6 48
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now