Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts

    Problem with update and select

    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);

  • #2
    Regular Coder Nischumacher's Avatar
    Join Date
    Oct 2005
    Location
    Bombay, India
    Posts
    196
    Thanks
    0
    Thanked 2 Times in 2 Posts
    try this
    Code:
    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)
    - NS 666
    .net DEVILoper

  • #3
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    That returns the error code "ORA-00927: missing equal sign".

  • #4
    Regular Coder Nischumacher's Avatar
    Join Date
    Oct 2005
    Location
    Bombay, India
    Posts
    196
    Thanks
    0
    Thanked 2 Times in 2 Posts
    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...
    - NS 666
    .net DEVILoper

  • #5
    Regular Coder
    Join Date
    Jan 2003
    Posts
    867
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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.

    Code:
    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

  • #6
    Regular Coder Nischumacher's Avatar
    Join Date
    Oct 2005
    Location
    Bombay, India
    Posts
    196
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Code:
    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'
    - NS 666
    .net DEVILoper


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •