Update Based on Select

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

LVL 1
Gary JonesDirector of Information TechnologyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
JacobfwConnect With a Mentor Commented:
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
 
johanntagleCommented:
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
 
Devinder Singh VirdiConnect With a Mentor Lead Oracle DBA TeamCommented:
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
 
Gary JonesDirector of Information TechnologyAuthor Commented:
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
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.

All Courses

From novice to tech pro — start learning today.