Oracle SQL

Hi Experts,
I need to update the same table with 4 attributes depending on the values in one of the other attribute. The table and values are below --

column_a              column_b           column_c                column_d

   abc1                      c:                           1      
   abc1                      d:                           1
   abc1                      e:                            1

  def1                        c:                           1
  def1                        d:                           0
  def1                        e:                            1


I want to update column_d based on the Logical 'AND' of column_c.
after the update the values in the table should be

column_a              column_b           column_c                column_d

   abc1                      c:                           1                                1
   abc1                      d:                           1                                1
   abc1                      e:                            1                                1

  def1                        c:                           1                                0
  def1                        d:                           0                                0
  def1                        e:                            1                               0

Is it possible to do this in a single SQL?  appreciate your expert advise.
smaligAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
It probably can be done, but you need to explain or show how  you come up with the value for COLUMN_D.

The "logical and" of COLUMN_C and what?  There needs to be 2 things involved in an AND.
smaligAuthor Commented:
logical AND of values for the same id in column_a. for example abc1 has 3 records with values 1 in column_c for all 3 records. I want to update column_d with value of 1 for all 3 records since logical AND of all 3 records is 1. I thought it as logical AND. basically I want a 1 in column_d if all values for a id in column_a has 1 or a value of 0 even if one of the value for an id in column_c is 0.
johnsoneSenior Oracle DBACommented:
To me, the easiest way is a 2 step process:
UPDATE tab1 
SET    column_d = 1; 
UPDATE tab1 a 
SET    column_d = 0 
WHERE  EXISTS (SELECT 1 
               FROM   tab1 b 
               WHERE  a.column_a = b.column_a 
                      AND b.column_c = 0); 

Open in new window

Set everything to 1 and then only set the exceptions to 0.

I'm sure it can be done in one complicated query, but this makes it easy.  Updates some rows twice, but it does the job.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
If you want a single statement, try this.  As long as col1 and col2 identify a unique record it should work:
/*
drop table tab1 purge;
create table tab1(col1 varchar2(5), col2 char(2), col3 number, col4 number);

insert into tab1 values('abc1','c:',1,null);
insert into tab1 values('abc1','d:',1,null);
insert into tab1 values('abc1','e:',1,null);
insert into tab1 values('def1','c:',1,null);
insert into tab1 values('def1','d:',0,null);
insert into tab1 values('def1','e:',1,null);
commit;
*/

merge into tab1 t1
using (
	select col1, col2, sum(case when col3=1 then 1 end) over(partition by col1 order by col1) total_ones, count(*) over(partition by col1 order by col1) total
	from tab1
) t2
ON (t1.col1=t2.col1 and t1.col2=t2.col2)
When Matched Then Update Set t1.col4=case when t2.total_ones=t2.total then 1 else 0 end
/

Open in new window

Mark GeerlingsDatabase AdministratorCommented:
I think this simple, one-step approach will work:

update your_table t1
set t1.column_d = (select min(t2.column_c) from your_table t2
where t2.column_a = t1.column_a);

Of course, this table design brings up the question: do you need column_d to be stored?  Or, is it better to a create view that includes column_d (plus the other three columns) and it derives the value for column_d dynamically using the same sub-query I suggested in the update statement?
awking00Information Technology SpecialistCommented:
I think the merge method could be simplified a little bit -
merge into table t using
 (select column_a, column_b, min(column_c) over (partition by column_a) as updt_value
  from table) x
on (t.column_a = x.column_a and t.column_b = x.column_b)
when matched then
update set t.column_d = x.updt_value
;
slightwv (䄆 Netminder) Commented:
>>I think this simple, one-step approach will work:

I knew there had to be an easier way...
awking00Information Technology SpecialistCommented:
Agreed, I didn't see markgeer's solution before I submitted, but it certainly is the simplest.
johnsoneSenior Oracle DBACommented:
Gotta say, I thought there was an easier way to do it too.  At least someone found it.

I believe that most of these solutions would benefit from an index on COLUMN_A, if one isn't there already.
Mark GeerlingsDatabase AdministratorCommented:
"...these solutions would benefit from an index on COLUMN_A".  I agree.  And an index on both column_a and column_c may help even more.
smaligAuthor Commented:
simple and works
slightwv (䄆 Netminder) Commented:
If your actual requirements don't match the data you posted and Mark's post will not work, then ignore the rest of this post...

Thanks for the points but I suggest you look at markgeer's solution:
http://www.experts-exchange.com/Database/Oracle/Q_28685472.html#a40812831

Based on the data you posted, it is likely the better choice.
Mark GeerlingsDatabase AdministratorCommented:
Those suggestions are simpler than the one I suggested?  They don't look simpler to me.  I agree that they will all likely work as requested.

(I didn't see slightwv's post before I posted this one.)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.