SQL Server - Update a whole column using Order By

Question

I'm using SQL Server and have Management Studio installed if this is relevant.

I would like to copy a whole column from one table to another, but the catch is that the table I must copy to needs to be ordered a certain way, as there is no common identity between these tables I could use to join them.

I have read these two questions:

and I tried to combine their answers as follows:

WITH cte AS
(
    -- I must specify TOP to use ORDER BY
    SELECT TOP(50000) *
    FROM TableToCopyTo
    ORDER BY ColumnUsedToOrder
)
UPDATE cte 
SET ColumnToCopyTo = (SELECT ColumnToCopyFrom FROM TableToCopyFrom)

When I try to execute this query, it returns the following error:

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.

I've tried looking up the error but couldn't find relevant information.

I would like to either understand why my query is wrong or find an alternative to achieve what I'm looking for.

The error message says it all... The UPDATE wants one value at the time, not a whole bunch. — jarlh, Apr 10 '18 at 07:36
Take a look at https://stackoverflow.com/questions/982919/sql-update-query-using-joins — jarlh, Apr 10 '18 at 07:38
@jarlh, I tried replacing the Update part with a "SELECT * FROM cte" and it indeed worked. That's unfortunate, I thought I could use an Update on this one. — Dastardly, Apr 10 '18 at 07:39
Btw, your username doesn't match your profile picture. Loki is the father of Jörmungandr. — jarlh, Apr 10 '18 at 07:44

score 0 · Answer 1 · answered Apr 10 '18 at 07:41

Provided that your TableToCopyFrom has equal to or more rows than TableToCopyTo I'd use something like:

WITH cte1 AS
(
  -- I must specify TOP to use ORDER BY
  SELECT TOP(50000) *
    , ROW_NUMBER() OVER (ORDER BY ColumnUsedToOrder) AS RwNr
  FROM TableToCopyTo
  ORDER BY ColumnUsedToOrder
), cte2 AS (
  SELECT *
    , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS RwNr -- or order by a column you know makes sense; this way it'll order it the way the data "sits" in the table, but it's not guaranteed
  FROM TableToCopyFrom
)
UPDATE cte1
SET ColumnToCopyTo = ColumnToCopyFrom
FROM cte1
  INNER JOIN cte2 ON cte1.RwNr = cte2.RwNr;

EDIT: please make sure you test this logic on some copy of the database first of course.

DineshDB · Accepted Answer · 2018-04-10T09:16:01.140

0

Try the following answer. You have to give the matching columns for the two tables.

;WITH cte AS
(
-- I must specify TOP to use ORDER BY
SELECT TOP(50000) *
FROM TableToCopyTo
ORDER BY ColumnUsedToOrder
)
UPDATE cte SET ColumnToCopyTo = ColumnToCopyFrom
FROM cte
JOIN TableToCopyFrom A
ON cte.ColumnName = A.EquvaliantColumnName

edited Apr 10 '18 at 09:16

answered Apr 10 '18 at 07:41

DineshDB

5,998
7
33
49

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 25 years** ago) and its use is discouraged – marc_s Apr 10 '18 at 08:23
@marc_s, Now its fine? Thanks for the suggestion. – DineshDB Apr 10 '18 at 09:16
Much better now, yes! :-) – marc_s Apr 10 '18 at 09:22

score 0 · Answer 3 · answered Apr 10 '18 at 07:50

you can use Row number as key as follows

    WITH tblDest AS
    (

    SELECT Row_Number() over(order by ColumnUsedToOrder) as RowNum, TOP(50000) *
    FROM TableToCopyTo
    ORDER BY ColumnUsedToOrder
    ),tblSrc as
    (
    select  Row_Number() over(order by (select null)) as RowNum,ColumntoCopyFrom 
    from TableToCopyFrom
    )

   update tblDest set ColumntoCopyto = ColumntoCopyFrom  
   FROM tblDest join tblSrc on tblDest.RowNum = tblSrc.RowNum

SQL Server - Update a whole column using Order By

3 Answers3