Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Update Based on Select

Posted on 2011-09-21
4
Medium Priority
?
339 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 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

783 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