?
Solved

plsql - update with select

Posted on 2014-10-22
6
Medium Priority
?
353 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1332 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 668 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1332 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

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!

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 take different types of Oracle backups using RMAN.
Suggested Courses

765 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