Copy values from one table to another in SQL

Question

I have 2 tables. I need to update all rows of table 1 with the values in specific columns from table 2. They have the same structure.

UPDATE @TempTable       
SET [MyColumn] =
    (
    SELECT [MyColumn]
    FROM 
        [udf_AggregateIDs] (@YearId) AS [af]
        INNER JOIN [MyForm] ON
        (
            [af].[FormID] = [MyForm].[FormID] AND
            [af].[FormID] = @MyFormId
        )
    WHERE [Description] = [MyForm].[Description]
    )

I get an error saying Subquery returned more than 1 value. I only added the where clause in because i thought sql is struggling to match the rows, but both tables have the same rows. It should return multiple values because i'm trying to copy across all rows for MyColumn from the one table to the other. Ideas?

Could you properly alias all the columns so we have some clue which table a column belongs to? Does the function expose `MyColumn` or does the `MyForm` table? Which table does `Description` belong to, `@TempTable` or the function? You may know your schema but your audience doesn't. If you want precise help on fixing your query, please provide a precise query. — Aaron Bertrand, Jun 25 '12 at 13:58

score 0 · Answer 1 · answered Jun 25 '12 at 10:53

0

is Description unique ?

select [Description], count(*) from [MyForm] group by [Description] having count(*)>1

answered Jun 25 '12 at 10:53

Nahuel Fouilleul

18,726
2
31
36

why would I select description? – newbie_86 Jun 25 '12 at 12:03
this query returns duplicates or nothing if there is no duplicate – Nahuel Fouilleul Jun 25 '12 at 12:45

score 0 · Answer 2 · edited May 23 '17 at 11:48

0

You don't need a sub query..just join the tables.. same type of question has been answered here. Hope it helps.

edited May 23 '17 at 11:48

Community

1
1

answered Jun 25 '12 at 11:00

Dimith

379
2
12

score 0 · Answer 3 · answered Jun 25 '12 at 14:03

Have to guess here because your query isn't self-documenting. Does MyColumn come from the function? Does @TempTable have a description column? Who knows, because you didn't prefix them with an alias? Try this. You may have to adjust since you know your schema and we don't.

UPDATE t 
  SET [MyColumn] = func.MyColumn -- have to guess here
  FROM dbo.[udf_AggregateIDs] (@YearId) AS func
  INNER JOIN dbo.MyForm AS f
  ON func.FormID = f.FormID
  INNER JOIN @TempTable AS t
  ON t.Description = f.Description -- guessing here also
WHERE f.FormID = @MyFormID;

Copy values from one table to another in SQL

3 Answers3