PDA

View Full Version : Problem with update and select



arnyinc
Jan 13th, 2006, 03:24 PM
I'm trying to update my hardware inventory (hwinventory) table with the model ID in my new Model table. I get the error "ORA-01427: single-row subquery returns more than one row". Is there any way to make it a multiple row subquery?

UPDATE hwinventory h SET model_id =
(SELECT m.id FROM MODEL m WHERE
m.mfg_id=h.mfg_id
AND m.name=h.description);

Nischumacher
Jan 13th, 2006, 03:34 PM
try this

UPDATE hwinventory h SET model_id in
(SELECT m.id FROM MODEL m WHERE
m.mfg_id=h.mfg_id
AND m.name=h.description)

arnyinc
Jan 13th, 2006, 03:38 PM
That returns the error code "ORA-00927: missing equal sign".

Nischumacher
Jan 13th, 2006, 04:07 PM
my mistake... i thought it was a SELECT query...

but... if your sub-query returned 1 row... then all the 'model_id' in 'hwinventory' table would have been updated... as there is no WHERE clause in your main query...

if it returns more than one row... where to update...

arnyinc
Jan 13th, 2006, 04:36 PM
Maybe I have the wrong idea with my query. If I have my hwinventory table with a "description" defined for each record, I would like to compare this description with the model name in my model table. If the description and model name are equal to eachother, then I want to look up the id in my model name table and update the hardware inventory table with this id.



hwinventory
ID description model_id
1 PowerEdge 2650 17 (this is currently null. I want it to be 17)
2 PowerEdge 2650 17 (this is currently null. I want it to be 17)
3 Proliant 1800 20 (this is currently null. I want it to be 20)

model
ID name
17 PowerEdge 2650
20 Proliant 1800

Nischumacher
Jan 13th, 2006, 05:02 PM
UPDATE hwinventory h
SET model_id = (SELECT m.id FROM MODEL m WHERE m.name='---')
WHERE h.description='---'
the '---' could be 'PowerEdge 2650' or 'Proliant 1800'