none
UPDATE statement: Subquery returned more than 1 value

    Question

  • I am struggling with syntax for an update statement.

    Goal: If a RelationID has invoice history then HasHistory=1

     

    UPDATE [Tbl_Relations]
    SET [HasHistory]= 1
    FROM    [Tbl_Relations] r INNER JOIN  
    (SELECT     RelationID
    FROM         vw_Invoice_history

    GROUP BY RelationID
    ) inv ON r.RelationID= inv.RelationID

     

    Result: Subquery returned more than 1 value

    What am I doing wrong?

     

    Thanks in advance,

    Frans

    Wednesday, October 03, 2007 7:07 PM

Answers

  • Try this:

     

    Code Block

    UPDATE [Tbl_Relations]

    SET [HasHistory]= 1

    FROM [Tbl_Relations] r

    where exists

    (SELECT 1

      FROM vw_Invoice_history inv

      WHERE r.RelationID= inv.RelationID

    )

     

     

     

    Wednesday, October 03, 2007 7:12 PM
  • Your code and Dale's code both should work fine and it works as outlined below for the data set that we assume. If your data is different than what is listed below, then you need to share that and its hard to guess from here. Post the ddl of the tables/views, data set and expected resultset to get resolutions quickly.

     

    Code Block

     

    use tempdb

     

    create table Tbl_Relations (RelationID int not null primary key, HasHistory int)

    create table vw_Invoice_history (RelationID int, cName varchar(20) default 'abcd')

    --For simplicity lets consider vw_Invoice_history as a table

     

    insert Tbl_Relations select 1, NULL

    insert Tbl_Relations select 2, NULL

    insert Tbl_Relations select 3, NULL

    insert Tbl_Relations select 4, NULL

    insert Tbl_Relations select 5, NULL

     

    insert vw_Invoice_history(RelationID) select 1

    insert vw_Invoice_history(RelationID) select 2

    insert vw_Invoice_history(RelationID) select 2

    insert vw_Invoice_history(RelationID) select 3

    insert vw_Invoice_history(RelationID) select 3

    insert vw_Invoice_history(RelationID) select 3

     

    select * from Tbl_Relations

    select * from vw_Invoice_history

     

    UPDATE [Tbl_Relations]

    SET [HasHistory]= 1

    FROM [Tbl_Relations] r INNER JOIN

    (SELECT RelationID

    FROM vw_Invoice_history

    GROUP BY RelationID

    ) inv ON r.RelationID= inv.RelationID

     

    UPDATE [Tbl_Relations]

    SET [HasHistory]= 1

    FROM [Tbl_Relations] r

    where exists

    (SELECT 1

    FROM vw_Invoice_history inv

    WHERE r.RelationID= inv.RelationID

    )

     

     

    Thursday, October 04, 2007 6:44 AM

All replies

  • Try this:

     

    Code Block

    UPDATE [Tbl_Relations]

    SET [HasHistory]= 1

    FROM [Tbl_Relations] r

    where exists

    (SELECT 1

      FROM vw_Invoice_history inv

      WHERE r.RelationID= inv.RelationID

    )

     

     

     

    Wednesday, October 03, 2007 7:12 PM
  • Frans,

     

    If you look at what just the subquery is doing, SELECT RelationID FROM vw_Invoice_History, there are no filters on that query and you will therefore return every row of data contained in the table.

     

    Whether the RelationID field is defined as an IDENT column or is updated via a program interface, there are obviously multiple values for that field.

     

    The only thing that I can see that you are doing wrong is not having a WHERE clause in your subquery; find a method to restrict the number of records you are returning to a single record and you will be fine.

     

    When I work with subqueries, I always run the subquery separately and add the necessary WHERE clause statements until I am returning only one record.

     

    I hope this helps.

    Richard

    Wednesday, October 03, 2007 7:14 PM
  • Still the same error message.

     

    Tbl_Relations has RelationID as primary key

    vw_Invoice_History has a group by on RelationID

     

    I also tried inner join instad of exists

     

    More suggestions?

     

    Frans

     

     

     

     

    Wednesday, October 03, 2007 7:33 PM
  • I still cannot get it to work.

    It defintely has something to do with the subquery returning more values.

     

    The where exist option from Dale does not work (same message).

    I think I understand the story from Richard, but it doesn't help me with the right syntax.

    Last night I have tried for several hours but still the same message

     

    Goal: If a RelationID has invoice history then HasHistory=1

    What should be the update statement?

     

    Help is very much appreciated.

    By the way: SQL 2000

     

    Frans

     

     

     

    Thursday, October 04, 2007 5:51 AM
  • Your code and Dale's code both should work fine and it works as outlined below for the data set that we assume. If your data is different than what is listed below, then you need to share that and its hard to guess from here. Post the ddl of the tables/views, data set and expected resultset to get resolutions quickly.

     

    Code Block

     

    use tempdb

     

    create table Tbl_Relations (RelationID int not null primary key, HasHistory int)

    create table vw_Invoice_history (RelationID int, cName varchar(20) default 'abcd')

    --For simplicity lets consider vw_Invoice_history as a table

     

    insert Tbl_Relations select 1, NULL

    insert Tbl_Relations select 2, NULL

    insert Tbl_Relations select 3, NULL

    insert Tbl_Relations select 4, NULL

    insert Tbl_Relations select 5, NULL

     

    insert vw_Invoice_history(RelationID) select 1

    insert vw_Invoice_history(RelationID) select 2

    insert vw_Invoice_history(RelationID) select 2

    insert vw_Invoice_history(RelationID) select 3

    insert vw_Invoice_history(RelationID) select 3

    insert vw_Invoice_history(RelationID) select 3

     

    select * from Tbl_Relations

    select * from vw_Invoice_history

     

    UPDATE [Tbl_Relations]

    SET [HasHistory]= 1

    FROM [Tbl_Relations] r INNER JOIN

    (SELECT RelationID

    FROM vw_Invoice_history

    GROUP BY RelationID

    ) inv ON r.RelationID= inv.RelationID

     

    UPDATE [Tbl_Relations]

    SET [HasHistory]= 1

    FROM [Tbl_Relations] r

    where exists

    (SELECT 1

    FROM vw_Invoice_history inv

    WHERE r.RelationID= inv.RelationID

    )

     

     

    Thursday, October 04, 2007 6:44 AM
  • Thanks Sankar,

     

    If I run your code it runs fine!

     

    I found out that if I make a copy of the table with relations and run my code on that table it works fine.

    But on the original table it gives the error message!

    So I know now it has something to do with the data itself.

     

    If you have any suggestions please let me know, but at least I know that the update statement is correct!

     

    Thanks,

    Frans

     

     

    Thursday, October 04, 2007 10:15 AM
  • Frans,

    You may want to check to see if there are any triggers on that table and if so what they are doing.
    That might be where the issues lies.
    Thursday, October 04, 2007 10:35 AM