Update parent record based on child condition

Hi,

Scenario : parent table = Model ; child table = Submodel ;
1 records in modelcan have many records in Submodel(foreign key here is fk_model)


Model                  Submodel
pk_model            pk_submodel
nulby                  fk_model
modelname            sModelName


Goal: update Model.nulby column with a constant say 'Changed' if

(1) "name1" exists in Submodel.sModelName column ("name1" could be one of the possible values of Submodel.sModelName column)

AND

(2) Model.modelName is null

AND

(3) There is only one other child attached to this Submodel's parent

EXAMPLE:
Values in model

pk_model      modelname      
1            m1                  
2            m2
3            NULL
4            m4
5            null
6            null


Values in Submodel

pk_submodel            sModelName      fk_model
11                  s1            1
12                  s2            1
13                  name1            3
14                  s4            3
15                  s5            4
16                  name1            5
17                  name22            6


I want to be able to update model table having pk_model value 5 when name1 is passed as a value to the model table in update stmt

similary model table would be update when sModelName      is name22. For any other value the model table should not be updated say if I pass s5 as  sModelName value
 model table should not be updated


Please sugest an efficient way to write updae stmt
shashirekhabkAsked:
Who is Participating?
 
Peter KwanAnalyst ProgrammerCommented:
Try this:
update model set  nulby = 'Changed' where pk_model in
(SELECT fk_model FROM
(SELECT fk_model, smodelname,  COUNT(pk_submodel) over (PARTITION BY fk_model) cnt FROM submodel s, MODEL p WHERE 
p.PK_MODEL = s.fk_model AND p.MODELNAME IS NULL) X WHERE
smodelname IN ('name1') AND cnt = 1)

Open in new window

0
 
shashirekhabkAuthor Commented:
Thank you .

This is very useful. However I am not sure if its efficient , as overtime the submodel table would hold one million records.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.