Solved

Update Based on Select

Posted on 2011-09-21
4
328 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
Oracle - SQL Where clause causing Invalid Number Error 4 44
run sql script from putty 4 62
pl/sql - query very slow 26 71
Fill Null values 5 28
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

790 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