Home » SQL & PL/SQL » SQL & PL/SQL » SQL : Update table based on the number of child attached (Oracle 10g)
SQL : Update table based on the number of child attached [message #400544] Tue, 28 April 2009 12:30 Go to next message
Messages: 1
Registered: June 2007
Junior Member

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
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
Re: SQL : Update table based on the number of child attached [message #400546 is a reply to message #400544] Tue, 28 April 2009 12:40 Go to previous message
Messages: 25544
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.


Please, please, please Read & Follow Posting Guidelines above.

Go to the section labeled "Practice" & do as directed.

Post DDL for tables.

Post DML for test data.

Post expected/desired results.
Previous Topic: Lost - is it possible
Next Topic: How to load oracle table data into a file
Goto Forum:

Current Time: Thu Jul 27 20:01:48 CDT 2017

Total time taken to generate the page: 0.11830 seconds