Post reply

how to update a table using update clause

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

  • January 30, 2011 at 10:20 pm

    #1280242

    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

  • January 31, 2011 at 10:53 am

    #1280522

    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

  • January 31, 2011 at 8:55 pm

    #1280725

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

  • February 1, 2011 at 8:43 am

    #1280976

    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)

  • February 1, 2011 at 10:58 am

    #1281083

    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.

  • February 1, 2011 at 8:43 pm

    #1281230

    Yeah...

    I agree.

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

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