Update parent record based on child condition

Posted on 2009-04-28
Last Modified: 2013-12-18

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)


(2) Model.modelName is null


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

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
Question by:shashirekhabk
    LVL 16

    Accepted Solution

    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 
    smodelname IN ('name1') AND cnt = 1)

    Open in new window


    Author Comment

    Thank you .

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

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now