how to update a table using update clause

Question

Post reply

how to update a table using update clause

rsk15

Newbie

Points: 3
More actions
January 30, 2011 at 9:59 pm

#230920

I have to display first 4 letters of a country in a column [country_sub], both country & country_sub have same data the objective is to take a reference from country column and update country_sub column
This is what I am doing
update test set country_sub = (select (substring(country,1,4)) from test where country like '%ENGLAND%')
Subquery returned more than 1 value .This is not permitted when the subquery follows =,
if I run this query using select statement independently it works fine displaying multiple rows which have above pattern but I get above error when I embed select in front of update.
I am new bee and need pointers on how to fix it.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

deepak.gupta 25154 SSC-Addicted Points: 461 More actions · Answer 1

Hi,

Please use below query, surely it will work for you... 🙂

update test set country_sub = (substring(country,1,4)) from test t where t.country like '%ENGLAND%' AND t.country = country

Dave Vick SSC Rookie Points: 40 More actions · Answer 2

The reason it is giving you the error is because you are trying to assign multiple values to a single row. If you run the SELECT statement by itself you could get more than one row back, if you do the query has no idea which value to assign to the column.

Why do you have the WHERE clause in there? Are you only trying to update certain rows, if so then you need to move the WHERE so it is outside of the subquery, something like this should work:

update test set country_sub = substring(country,1,4) where country like '%ENGLAND%'

That will update the country_sub column of every row whose country value has 'ENGLAND' in it somewhere.

Hope that helps

deepak.gupta 25154 SSC-Addicted Points: 461 More actions · Answer 3

Hi Dave, You are right that "it is giving you the error is because you are trying to assign multiple values to a single row." But if this is the requirement then the where clause should have one more condition which I mentioned in my previous reply. For your reference you can see the below query and definetly it will work.

update test set country_sub = (substring(country,1,4)) from test t where t.country like '%ENGLAND%' AND t.country = country

Hope now its clear:-)

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

rsk15 (1/30/2011)
I have to display first 4 letters of a country in a column [country_sub], both country & country_sub have same data the objective is to take a reference from country column and update country_sub column
This is what I am doing
update test set country_sub = (select (substring(country,1,4)) from test where country like '%ENGLAND%')
Subquery returned more than 1 value .This is not permitted when the subquery follows =,
if I run this query using select statement independently it works fine displaying multiple rows which have above pattern but I get above error when I embed select in front of update.
I am new bee and need pointers on how to fix it.

I have to ask... what are you going to do for countries that begin with words like North, South, East, or West? Multiple countries will appear the same.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Dave Vick SSC Rookie Points: 40 More actions · Answer 5

Thanks Deepak, I wasn't questioning you answer, just giving the OP another option and explaining to them why they got the error message. Without more info from them on what they are trying to accomplish we can't say for sure exactly which approach is right.

deepak.gupta 25154 SSC-Addicted Points: 461 More actions · Answer 6

deepak.gupta 25154

SSC-Addicted

Points: 461

February 1, 2011 at 8:43 pm

#1281230

Yeah...

I agree.