Logic for backward updation based on parent value

I have below tables with data as below

Table_Cell

Cell_id  Column_id   row_id SwitchCell
------------------------------
1122    11      22      1
1133    11      33      0
1144    11      44      1
5522    55      22      1
5533    55      33      1
5544    55      44      1

Table_column

column_id    SwitchCol
11          ?
55          ?

Table_row

row_id  Switchrow
22      ?
33      ?
44      ?

I need to update SwitchCol in Table_column and Switchrow in Table_row

Logic is

If for an column_id in Table_column, all the combination with row_id in Table_row,present in table Table_Cell is 1, then make the SwitchCol =1 for that column_id Similary, for an id in Table_row, all the combination of Table_row with id in Table_column in table Table_Cell is 1, then make Switchrow=1 for that row_id

Eg: For column_id =55 , its combination with Table_row table in table Table_Cell (5522,5533,5544) has SwitchCell =1, so SwitchCol for 55 will be 1, But SwitchCol for 11 will be 0 (as (1122,1133,1144) has 1,0,1 which is off in one case.

Please help me in the logic. Looking for sql query

Answer

I think you just want min() and a correlated subquery:

update table_column co
set switchcol = (
    select min(ce.switchcell)
    from table_cell ce
    where ce.column_id = co.column_id
)

Advertisement

Answer