smalig
asked on
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.
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
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
;
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
;
>>I think this simple, one-step approach will work:
I knew there had to be an easier way...
I knew there had to be an easier way...
Agreed, I didn't see markgeer's solution before I submitted, but it certainly is the simplest.
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.
I believe that most of these solutions would benefit from an index on COLUMN_A, if one isn't there already.
"...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.
ASKER
simple and works
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:
https://www.experts-exchange.com/questions/28685472/Oracle-SQL.html?anchorAnswerId=40812831#a40812831
Based on the data you posted, it is likely the better choice.
Thanks for the points but I suggest you look at markgeer's solution:
https://www.experts-exchange.com/questions/28685472/Oracle-SQL.html?anchorAnswerId=40812831#a40812831
Based on the data you posted, it is likely the better choice.
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.)
(I didn't see slightwv's post before I posted this one.)
The "logical and" of COLUMN_C and what? There needs to be 2 things involved in an AND.