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
bksr
Messages: 1
Registered: June 2007
Junior Member
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
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
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.

http://www.orafaq.com/forum/t/88153/0/

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: Fri Dec 02 22:37:05 CST 2016

Total time taken to generate the page: 0.10034 seconds