sql - UPDATE statement in an inheritance based table -

i've got table that's meant used modeling inheritance relationship. it's column oriented have columns properties , looks like:

mainid, subid, prop1, prop2, etc. 

all rows same mainid related , row subid=0 parent row matches mainid , subid > 0. thus:

100, 0, 'abc', 123  100, 1, null, 456 

means (100,1) child of (100,0) , querying prop1 (100,1) should give me 'abc' , querying prop2 (100,1) should give me 456.

this seems work ok however, when want update (100,1).prop1, i'd make field null if update value 'abc' (matches parent.prop1 field). there simple update query can this? realize can on multiple queries 1 update query , updating many fields @ once, if possible (eg update (100,1).prop1='abc', (100,1).prop2=789, etc.)

this should work standard sql (eg not specific particular sql engine)

use case expression , dependent subquery, part of standard ansii sql , supported databases.:

update table t1 set prop1 = case when exists                   ( select * table t2                    t1.mainid = t2.mainid                       , t2.subid = t1.subid - 1                       , t2.prop1 = 'abc'                  )              null else 'abc' end t1.mainid = 100 , t1.subid = 1