Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2005
    Posts
    40

    Unanswered: update more than 1 row

    I am trying to update and
    update
    set=
    (select ....);

    and in my select returns more than 1 rows(MSGubquery returns more than 1 rows).
    For example if there are more than one same value in a cell that matches with select (4,4,4,4,4,4,4...) i want to put only the '4' in the value and not all the 4's!Thats why it hits an error but i dont know how to put only one of the same values in the cell.
    Any idea?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If all of the multiple values are the same, DISTINCT might be OK for you:

    UPDATE table_name SET
    column_name = (SELECT DISTINCT another_column_name FROM ...);

  3. #3
    Join Date
    Nov 2005
    Posts
    40
    Quote Originally Posted by Littlefoot
    If all of the multiple values are the same, DISTINCT might be OK for you:

    UPDATE table_name SET
    column_name = (SELECT DISTINCT another_column_name FROM ...);
    What does distinct do in update query??
    I have tried it but the same message appers : Subquery returns more than 1 rows

  4. #4
    Join Date
    Nov 2002
    Posts
    272
    Distinct causes only distinct values to be returned. If before, you got a bunch of 4's, DISTINCT will only return one 4. If you still get the same error as before, you get at least 2 different values. You need to narrow down your subquery so that it returns only 1 value.

  5. #5
    Join Date
    Nov 2005
    Posts
    40
    Quote Originally Posted by ivon
    Distinct causes only distinct values to be returned. If before, you got a bunch of 4's, DISTINCT will only return one 4. If you still get the same error as before, you get at least 2 different values. You need to narrow down your subquery so that it returns only 1 value.
    Yes i believe that distinct doesnt solve the problem because i want to to return multiple values but not in a single row but in each value separetelly.For example i want to put 4 in one cell , one 4 in another and so on.

    You are right i have to narrow down this subquery but i dont know how...

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It is usually done using the WHERE clause in the subquery. For example,
    Code:
    UPDATE table_name t SET
    t.column_name = (SELECT t1.another_column_name 
                     FROM another_table t1
                     WHERE t1.some_id = t.some_id
                       AND t1.date_column = (SELECT MAX(t2.date_column) FROM another_table t2
                                             WHERE t2.some_id = t1.some_id
                                            )
                    );
    As you didn't provide input data set, nor tables' description, it isn't easy to give you a better advice.

  7. #7
    Join Date
    Nov 2005
    Posts
    40
    Quote Originally Posted by Littlefoot
    It is usually done using the WHERE clause in the subquery. For example,
    Code:
    UPDATE table_name t SET
    t.column_name = (SELECT t1.another_column_name 
                     FROM another_table t1
                     WHERE t1.some_id = t.some_id
                       AND t1.date_column = (SELECT MAX(t2.date_column) FROM another_table t2
                                             WHERE t2.some_id = t1.some_id
                                            )
                    );
    As you didn't provide input data set, nor tables' description, it isn't easy to give you a better advice.
    I want to update or to insert some empty country names regarding of some strings of places.The strings include every country like "Athens,Airport-Greece" or "State Building 45 -Netherlands" or like that.So with a like '% %' i want to insert a row like this :
    234 | Athens,Airports-Greece | Greece .The 3rd(Greece is the empty value where i want to put Greece).The problem is that there are 40-50 similar strings that contain the name Greece and with the update function it puts these 40-50 same in the same row!!!
    How can i enter only one and not all the 40-50 ??? !!!
    Sorry if my explanation is not quite enough to understand it...

Posting Permissions

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