Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98

    Unanswered: Need some help with update query

    Hi There ,

    I run the query below and get the error message below the query.
    How can i do this update?

    Code:
    UPDATE GEO_Plaats  
    SET NetNummer = (SELECT GEO_Netnummers.Netnummer
    FROM GEO_Netnummers
    WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats) 
    WHERE EXISTS
      (SELECT GEO_Netnummers.Netnummer
       FROM GEO_Netnummers
       WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats);

    ERROR:

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    The statement has been terminated.


    Cheers Wimmo

  2. #2
    Join Date
    Dec 2004
    Location
    California, USA
    Posts
    93
    This is untested but it might be of assistance.

    UPDATE GEO_Plaats
    SET NetNummer = n.Netnummer
    FROM GEO_Plaats p
    JOIN GEO_Netnummers n
    ON p.Plaats = n.Plaats

    Good Luck!
    Whoever imagines himself a favorite with God holds others in contempt. - Robert Green Ingersoll, lawyer and orator (1833-1899)

  3. #3
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    As you see problem is in your subquery
    Code:
    (SELECT GEO_Netnummers.Netnummer
    FROM GEO_Netnummers
    WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats)
    which returns more than 1 record from GEO_Netnummers table for specific Plaats in GEO_Plaats table. Simply speaking one "Plaat" from GEO_Plaats have more records in GEO_Netnummers table (cardinality is 1:N)

    If all records in GEO_Netnummers have the same Netnummer for specific GEO_Plaats.Plaats you can use distinct to retrive 1 record:
    Code:
    UPDATE GEO_Plaats  
    SET NetNummer = (SELECT DISTINCT GEO_Netnummers.Netnummer
    FROM GEO_Netnummers
    WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats) 
    WHERE EXISTS
      (SELECT GEO_Netnummers.Netnummer
       FROM GEO_Netnummers
       WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats);
    If GEO_Netnummers.Netnummer differs for specific GEO_Plaats.Plaats you have 2 options:
    1) use max() or min() function to retrieve 1 record. But in this case you have to realise if your subquery returns Netnummers: 1, 2, 5, 70 you select max() it means 70 but what if 5 is correct one?
    Code:
    UPDATE GEO_Plaats  
    SET NetNummer = (SELECT max(GEO_Netnummers.Netnummer)
    FROM GEO_Netnummers
    WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats) 
    WHERE EXISTS
      (SELECT GEO_Netnummers.Netnummer
       FROM GEO_Netnummers
       WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats);
    2) use another restrictions in your WHERE clause to retrieve 1 unique number. This restrictions depend on your "business'' rules
    Code:
    UPDATE GEO_Plaats  
    SET NetNummer = (SELECT GEO_Netnummers.Netnummer
    FROM GEO_Netnummers
    WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats
    AND GEO_Netnummers.SOMETHING = SOMETHING
    ....) 
    WHERE EXISTS
      (SELECT GEO_Netnummers.Netnummer
       FROM GEO_Netnummers
       WHERE GEO_Netnummers.Plaats = GEO_Plaats.Plaats);
    It seems to me, Netnummer is primary key in GEO_Netnummers table, so I think DISTINC won't help. In this case I'd use 2. scenario and option 2

  4. #4
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by madafaka
    As you see problem is in your subquery
    [...]
    which returns more than 1 record from GEO_Netnummers table for specific Plaats in GEO_Plaats table. Simply speaking one "Plaat" from GEO_Plaats have more records in GEO_Netnummers table (cardinality is 1:N)
    It might be a data problem. The table and column names indicate this is about the Dutch phone system; plaats = city, netnummer = area code.

    As far as I know, a 'plaats' is supposed to have exactly 1 'netnummer'; several 'plaatsen' may share a 'netnummer'. If that's true and this error occurs, there may be invalid data in the GEO_netnummers table.

    The error might also occur if the joining is done on the city's name; several cities may exist with identical names but different 'netnummers'.

Posting Permissions

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