Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2003
    Posts
    13

    Unanswered: UPDATE statement

    Hi guys,

    Here's a simple problem I have. I'm trying to overwrite/replace every row in a column in one table, with rows in a column from another table. There are 200 rows in each table.

    Here's the statement that I wrote:

    update item
    set [description] = (select [basic description] from old_item)

    And here's the error I get:


    Server: Msg 512, Level 16, State 1, Line 1
    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.


    How woud you modify the statement in order to achieve that? Am
    I using the right approach or am I on the wrong track all together?

    Please advise.

  2. #2
    Join Date
    May 2003
    Location
    Rochester, NY
    Posts
    135

    Re: UPDATE statement

    update item
    set [description] = (select [basic description] from old_item)

    Your query "(select [basic description] from old_item)"
    is what the error message is referring to. This query will return
    a value for each row in that table. Since you can't assign all of
    these values to one column of one row in a table you receive the
    error.

    Are you trying to set all the columns to the same value or is there
    a correlation between the rows of each table?

  3. #3
    Join Date
    Oct 2003
    Posts
    13
    Rocket39,

    There are no correlations between any of the rows in the two tables. What I wanna do is to replace the values of a PARTICULAR column in one table, with data from a column in the other table. There are exactly 200 rows in each table. And I just want the value for each row in item.[description] to be replaced with the corresponding row from old_item.[basic description], respectively from first row to last. How would I go about doing that?

    Thanks,

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Tom Bombadill
    Rocket39,

    There are no correlations between any of the rows in the two tables. What I wanna do is to replace the values of a PARTICULAR column in one table, with data from a column in the other table. There are exactly 200 rows in each table. And I just want the value for each row in item.[description] to be replaced with the corresponding row from old_item.[basic description], respectively from first row to last. How would I go about doing that?

    Thanks,
    If 'There are no correlations between any of the rows in the two tables. ' - how are you going to set rules for copying data between tables? It has to be any kind of relations (correlations ) - id or something.

  5. #5
    Join Date
    Oct 2003
    Posts
    13
    Somebody suggested the following:

    UPDATE item
    SET [description] =
    (SELECT [basic description]
    FROM old_item
    WHERE item_no = item.item_no)

    item_no being the id column in the two tables. The problem is that the item_no values are different in the two tables. They do not correspond to one another. What to do then?


    Thanks for your help.

  6. #6
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by Tom Bombadill
    Somebody suggested the following:

    UPDATE item
    SET [description] =
    (SELECT [basic description]
    FROM old_item
    WHERE item_no = item.item_no)

    item_no being the id column in the two tables. The problem is that the item_no values are different in the two tables. They do not correspond to one another. What to do then?


    Thanks for your help.
    Save your tables to temporary tables with identity and rewrite query above for temporary tables.

  7. #7
    Join Date
    Oct 2003
    Posts
    13
    Snail,

    That's exacly my problem. There are no unique and corresponding columns of data between the two tables. But I'm sure there must be a way of doing this! Any ideas out there?

    Thanks

  8. #8
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136

    Thumbs up

    if there is no corresponding columns in the two tables and you just need to move a.1 to b.1, a.2 to b.2 etc etc copy the data in excel and do a manual copy/paste.

  9. #9
    Join Date
    Oct 2003
    Posts
    13
    Snail,

    I'm always one step behind you

    Can you elaborate on that a little, please?

    Thanks

  10. #10
    Join Date
    Oct 2003
    Posts
    13
    Rohit,

    That would take forever to do man And then, what would you do if you had a bigger database.


    Thanks

  11. #11
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    how about using 2 cursors to store data from table1 and table2, and then fetching rows 1 at a time from both and updating table1 with value from table2?
    Last edited by rohitkumar; 10-30-03 at 17:06.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    tom, if there are really no correlations between these tables, how do you propose to match the column value for row 187? i mean, how will you know which is row 187 in the other table?

    in other words, position means nothing, all rows have the same position, i.e. no position at all, until you select them, and if you do not supply an ORDER BY clause, then they can come out in any order whatsoever

    so if you update table 1 in any order whatsoever with the values from table 2 in any order whatsoever, and if that's good enough for you, then we're in business, and i can help you get the job done

    do what snail said, feed the two tables into temporary tables so that they each get an IDENTITY value assigned

    if you can think of a good ORDER BY clause while you're at it, so much the better!!!

    then do the joined update based on the identity values


    rudy
    http://r937.com/

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your problem is that you have no way of associating the data in the new table with the data in the old table. You need to read up on indexes and keys. Yet, there may be a way to save your butt.

    You say you want to replace the values from "first row to last". SQL server does not guarantee the order of any table unless you specify a clustered index, or some other index to sort on. So "first row to last" is virtually meaningless in SQL Server.

    If you are lucky, then when you open both these table they display the exact same number of rows in the exact same order. This means that (currently) they are stored in identical order. There is no guarantee they will stay that way.

    Open up each table in design mode, and at a field at the end called ROWKEY. Set its type to INT, and in the properties for the field set IDENTITY to "Yes", IDENTITY SEED to 1, and IDENTITY INCREMENT to 1.
    Save both table and then open them up in view mode. You should see all your rows numbered in sequential order (did I mention you need to keep your fingers crossed when you save the tables?).

    If all went well, you can now transfer the data using the following statement:

    Update item
    set [description] = old_item.[basic description]
    from item
    inner join old_item on item.ROWKEY = old_item.ROWKEY

    Good luck.

    blindman

  14. #14
    Join Date
    Oct 2003
    Posts
    13
    Thank you all for your input, gentlemen.

    Here's what I finally did, just to update you on what happened.

    I suppose the problem could have been solved by defining cursors and then reading the data from them. But with my limited tsql skills, I wasn't too successful at that. I tried reseeding the id column in the source table by using dbcc checkident. But for some reason it would not take effect, although I'm sure I executed it correctly. So what I had to do was to suck out all the fields (- id) and insert them into a new talbe where the id count would start from 1. And then I was able to run all kinds of update and insert and selects from the new source table. I guess similar to what Snail suggested.


    I guess a more experienced tsql programmer would have got over this in a jiffy. But not me. I had to take the long way. But thanks for all your suggestions. They put good ideas in my head.

Posting Permissions

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