Solved

Update Based on Select

Posted on 2011-09-21
4
325 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
4 Comments
 
LVL 7

Accepted Solution

by:
Jacobfw earned 70 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 55 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

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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Number Format 1 44
Need help with Oracle syntax 4 41
select query - oracle 16 81
File generation using utl_file 4 28
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

707 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

12 Experts available now in Live!

Get 1:1 Help Now