Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update Based on Select

Posted on 2011-09-21
4
Medium Priority
?
336 Views
Last Modified: 2012-05-12
I have a select statement that returns rows that need to be updated. I only need to update one column in the table. How can I update a table based on a select statement? I need to update the JCAT_INV_COMM_AMT to 0. I have included the SELECT statement, my concern is the join that I have (non ANSI, I prefer to keep it non ANSI).

Thanks in advance.
select 
    jcat_comp_code
    ,jcat_job_code
    ,jcat_phs_code
    ,jcat_code
    ,jcat_obudg_amt
    ,jcat_budg_amt
    ,jcat_cost_amt
    ,jcat_bill_amt
    ,jcat_inv_comm_amt
    ,jcat_ninv_comm_amt
    ,jcat_proj_amt
    ,jcat_oraseq
from
    da.jcjobcat 
    ,(select 
        jcdt_comp_code
        ,jcdt_job_code
        ,jcdt_phs_code
        ,jcdt_cat_code            
        ,jcdt_vdist_vou_num        
    from da.jcdetail
    where jcdt_vdist_vou_num in (40811
                        ,33150
                        ,33146
                        ,40824
                        ,40258
                        ,40257
                        ,40256
                        ,40255
                        ,40254
                        ,40253
                        ,40252
                        ,40349
                        ,40348
                        ,40259
                        ,40826
                        ,40825
                        ,40289
                        ,40827
                        ,40828
                        ,40829
                        ,33132
                        ,40833
                        ,40302
                        ,40834
                        ,40303
                        ,40304
                        ,40308
                        ,40310
                        ,40309
                        ,40388
                        ,40389
                        ,40408
                        ,40416
                        ,40409
                        ,40411
                        ,40412
                        )
    ) jcdt
where 
    jcat_comp_code = jcdt_comp_code
and jcat_job_code = jcdt_job_code
and jcat_phs_code = jcdt_phs_code
and jcat_code = jcdt_cat_code
group by jcat_comp_code
    ,jcat_job_code
    ,jcat_phs_code
    ,jcat_code
    ,jcat_obudg_amt
    ,jcat_budg_amt
    ,jcat_cost_amt
    ,jcat_bill_amt
    ,jcat_inv_comm_amt
    ,jcat_ninv_comm_amt
    ,jcat_proj_amt
    ,jcat_oraseq
order by 1,2,3,4
;

Open in new window

0
Comment
Question by:Gary Jones
[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
4 Comments
 
LVL 7

Accepted Solution

by:
Jacobfw earned 210 total points
ID: 36577513
update da.jcjobcat a
set jcat_inv_comm_amt = 0
where exists (select ....existing select from above.....
                       remove the group by
                       remove the order by
                       plus where a."primary key" = b."primary key") --- this may be several if a concatinated key
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36577533
What is the primary key of the table to be updated?  Make sure that it is part of the select statement.  Then put it to an SQL script, i.e.

DECLARE
  CURSOR select_cur is <put select statement here>;
BEGIN
  FOR cur in select_cur loop
    UPDATE table_name SET column_name=0 where primary_key_column=cur.primary_key_column;
  end loop;
  commit;
END;

Not sure if it can be done in one statement.  Regarding your join, you should be able to transform it to an ANSI join easily i.e

from da.jcjobcat inner join (subselect here) jcdt on ....

0
 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 165 total points
ID: 36578073
General syntax

update table1 t1
set (col1, col2) = ( select  x, y from table2 t2 where t1.pk1 = t2.pk1 and t1.xyz in (1,2,3))
where t1.xyz in (1,2,3)

0
 
LVL 1

Author Closing Comment

by:Gary Jones
ID: 36581244
I used a combination of two expert comments to accomplish what I needed. Here is the code I ended up using:

update da.jcjobcat
set jcat_inv_comm_amt = 0
where jcat_oraseq in (
    (
     -- JCJOBCAT QUERY
    select        
        jcat_oraseq
    from
        da.jcjobcat
        ,(select
            jcdt_comp_code
            ,jcdt_job_code
            ,jcdt_phs_code
            ,jcdt_cat_code            
            ,jcdt_vdist_vou_num        
        from da.jcdetail
        where jcdt_vdist_vou_num in ( << omited_for_space>> )
        ) jcdt
    where
        jcat_comp_code = jcdt_comp_code
    and jcat_job_code = jcdt_job_code
    and jcat_phs_code = jcdt_phs_code
    and jcat_code = jcdt_cat_code
    group by jcat_oraseq
    )
 )
;

The primary key on the two tables are x_comp_code, x_job_code, x_phs_code, and x_cat_code

Where x = jcat and jcdt.

Thanks for the help!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

704 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