Solved

plsql - update with select

Posted on 2014-10-22
6
338 Views
Last Modified: 2014-10-22
please

update tcc
set tcc.iscalculounitario = 1
--select *
from ta_cuenta_contable tcc
     inner join stage_ctactble_iscostounit sci on tcc.codigocuentacontable = sci.cuenta
where tcc.idsociedad = 1    

i have this error
ORA- 009333
0
Comment
Question by:enrique_aeo
  • 3
  • 2
6 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 333 total points
ID: 40396933
You cannot use a select like that.

What is it you are wanting to do?

You can do:
update tcc
 set tcc.iscalculounitario = 1
where some_column in (
select some_value
 from ta_cuenta_contable tcc
      inner join stage_ctactble_iscostounit sci on tcc.codigocuentacontable = sci.cuenta
 where tcc.idsociedad = 1    
)

or

update tcc
 set tcc.iscalculounitario = (
select some_value
 from ta_cuenta_contable tcc
      inner join stage_ctactble_iscostounit sci on tcc.codigocuentacontable = sci.cuenta
 where tcc.idsociedad = 1
)

but not the way you have it.


That said:
When I see an update and a select, I immediately think the MERGE command:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 167 total points
ID: 40396941
please read this article for your options to write such things, as also indicated above:
http://www.experts-exchange.com/Database/Miscellaneous/A_1517-UPDATES-with-JOIN-for-everybody.html
0
 

Author Comment

by:enrique_aeo
ID: 40396949
i execute this
update tcc
 set tcc.iscalculounitario = 1
where tcc.codigocuentacontable in (
select sci.cuenta
 from ta_cuenta_contable tcc
      inner join stage_ctactble_iscostounit sci on tcc.codigocuentacontable = sci.cuenta
 where tcc.idsociedad = 1    
)

but i have this error: table or view does not exists
in this line: update tcc
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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 333 total points
ID: 40396962
That was a quick copy/paste from your original post.

Look here:  update tcc

What table are you updating?  Just correct the update statement.
0
 

Author Comment

by:enrique_aeo
ID: 40396980
this work, thanks
update ta_cuenta_contable
   set ta_cuenta_contable.iscalculounitario = 1
 where ta_cuenta_contable.idsociedad = 1 and
       ta_cuenta_contable.codigocuentacontable in
       (select sci.cuenta
          from ta_cuenta_contable tcc
         inner join stage_ctactble_iscostounit sci on tcc.codigocuentacontable =
                                                      sci.cuenta
         where tcc.idsociedad = 1)
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40396991
Not sure you need the inner join.

See if this also does the same thing:
update ta_cuenta_contable tcc
    set iscalculounitario = 1
  where idsociedad = 1 and
        codigocuentacontable in
        (select sci.cuenta
           from stage_ctactble_iscostounit sci where tcc.codigocuentacontable = sci.cuenta
        )
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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

828 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