Update parent record based on child condition

Posted on 2009-04-28
Medium Priority
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

Peter Kwan earned 150 total points
ID: 24257265
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

ID: 24258815
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

807 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