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
enrique_aeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
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
PortletPaulfreelancerCommented:
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
PortletPaulfreelancerCommented:
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
PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.