update more than 1 row

archVille · #1 (**permalink**) 11-19-05, 05:12

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

and in my select returns more than 1 rows(MSG

ubquery 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?

Littlefoot · #2 (**permalink**) 11-19-05, 05:18

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

archVille · #3 (**permalink**) 11-19-05, 10:37

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

ivon · #4 (**permalink**) 11-19-05, 14:50

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.

archVille · #5 (**permalink**) 11-20-05, 05:49

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...

Littlefoot · #6 (**permalink**) 11-20-05, 06:27

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.

archVille · #7 (**permalink**) 11-20-05, 10:15

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...

Internet Services

Web Development

Digital Marketing

Consumer Tech