Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

PLSQL: update with dense_rank()

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
enrique_aeo
Asked:
enrique_aeo
  • 8
  • 6
  • 6
  • +1
4 Solutions
 
PortletPaulCommented:
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
 
chaauCommented:
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
 
enrique_aeoAuthor Commented:
i have this error
ORA-00933: SQL command not properly ended
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PortletPaulCommented:
sorry that isn't legitimate Oracle syntax
0
 
enrique_aeoAuthor Commented:
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
 
chaauCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
enrique_aeoAuthor Commented:
0
 
PortletPaulCommented:
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
 
chaauCommented:
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
 
enrique_aeoAuthor Commented:
both query with the MERGE
i have this error
ORA-00905: missing keyword
0
 
enrique_aeoAuthor Commented:
i have this version

Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
0
 
chaauCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
enrique_aeoAuthor Commented:
i have this error:
ORA-01732.png
0
 
chaauCommented:
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
 
enrique_aeoAuthor Commented:
the table has more than 5,000 records, tell me how I can create a primary key
0
 
PortletPaulCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
chaauCommented:
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
 
PortletPaulCommented:
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
 
enrique_aeoAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
PortletPaulCommented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 8
  • 6
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now