Solved

plsql - update with select

Posted on 2014-10-22
6
326 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 76

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 142

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 76

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 76

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL Convert to PL/SQL 23 81
PL/SQL LOOP CURSOR 3 59
File generation using utl_file 4 46
Oracle 12c database link between pdb not working 20 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…
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…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

863 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

20 Experts available now in Live!

Get 1:1 Help Now