Simple Update Statement So That All Rows Are Assigned a Different Value

Simple update statement so that all rows are assigned a different value

On some test table my end your original plan looks as follows.

It just calculates the result once and caches it in a sppol then replays that result. You could try the following so that SQL Server sees the subquery as correlated and in need of re-evaluating for each outer row.

UPDATE table1
SET    table2Id = (SELECT TOP 1 table2Id
                   FROM   table2
                   ORDER  BY Newid(),
                             table1.table1Id)

For me that gives this plan without the spool.

It is important to correlate on a unique field from table1 however so that even if a spool is added it must always be rebound rather than rewound (replaying the last result) as the correlation value will be different for each row.

If the tables are large this will be slow as work required is a product of the two table's rows (for each row in table1 it needs to do a full scan of table2)

Updating multiple rows with different values

Rather than doing case variable when value then ..., try doing case when condition then ... - like so:

UPDATE users
    SET value = CASE 
        WHEN id in (1,4) THEN 53
        WHEN id = 2 THEN 65
        WHEN id in (3,5) THEN 47
    END
WHERE id IN (1,2,3,4,5)

Update multiple rows with different values in a single SQL query

There's a couple of ways to accomplish this decently efficiently.

First -

If possible, you can do some sort of bulk insert to a temporary table. This depends somewhat on your RDBMS/host language, but at worst this can be accomplished with a simple dynamic SQL (using a VALUES() clause), and then a standard update-from-another-table. Most systems provide utilities for bulk load, though

Second -

And this is somewhat RDBMS dependent as well, you could construct a dynamic update statement. In this case, where the VALUES(...) clause inside the CTE has been created on-the-fly:

WITH Tmp(id, px, py) AS (VALUES(id1, newsPosX1, newPosY1), 
                               (id2, newsPosX2, newPosY2),
                               ......................... ,
                               (idN, newsPosXN, newPosYN))

UPDATE TableToUpdate SET posX = (SELECT px
                                 FROM Tmp
                                 WHERE TableToUpdate.id = Tmp.id),
                         posY = (SELECT py
                                 FROM Tmp
                                 WHERE TableToUpdate.id = Tmp.id)

WHERE id IN (SELECT id
             FROM Tmp)

(According to the documentation, this should be valid SQLite syntax, but I can't get it to work in a fiddle)

How to update each row of a column from one table with a list of values from another?

Given the following table structure:

CREATE TABLE #tempA (stringEntry NVARCHAR(50));
INSERT INTO #tempA (stringEntry) VALUES ('abcd'), ('efgh'), ('ijkl');

CREATE TABLE #tempB (stringEntry NVARCHAR(50));
INSERT INTO #tempB (stringEntry) VALUES ('mnop'), ('qrst'), ('uvwx');

You can do the following:

SELECT
  ROW_NUMBER() OVER(ORDER BY #tempA.stringEntry) AS RowNumber,
  #tempA.stringEntry AS entryA
INTO #tempA2
FROM #tempA;

SELECT
  ROW_NUMBER() OVER(ORDER BY #tempB.stringEntry) AS RowNumber,
  #tempB.stringEntry AS entryB
INTO #tempB2
FROM #tempB;

UPDATE #tempA 
SET #tempA.stringEntry = #tempB2.entryB
FROM #tempA
INNER JOIN #tempA2 ON #tempA.stringEntry = #tempA2.entryA
INNER JOIN #tempB2 ON #tempB2.RowNumber = #tempA2.RowNumber;

This assumes that you have equal number of rows in each table, as you indicated, or are okay with having the "excess" entries in your first table not being updated.

Update int column in table with unique incrementing values

declare @i int  = (SELECT ISNULL(MAX(interfaceID),0) + 1 FROM prices)

update prices
set interfaceID  = @i , @i = @i + 1
where interfaceID is null

should do the work

How to update a column via Row_Number with a different value for each row?

You can update all the rows in one statement using a CTE as below.

;WITH T
     AS (SELECT IdDatoLegal,
                Row_number() OVER (ORDER BY IdCliente ) AS RN
         FROM   dbo.DatosLegales)
UPDATE T
SET    IdDatoLegal = RN 

SQL UPDATE SET one column to be equal to a value in a related table referenced by a different column?

update QuestionTrackings q
inner join QuestionAnswers a
on q.AnswerID = a.AnswerID
set q.QuestionID = a.QuestionID
where q.QuestionID is null -- and other conditions you might want

I recommend to check what the result set to update is before running the update (same query, just with a select):

select *
from QuestionTrackings q
inner join QuestionAnswers a
on q.AnswerID = a.AnswerID
where q.QuestionID is null -- and other conditions you might want

Particularly whether each answer id has definitely only 1 associated question id.

mysql how to update a column of every row with a given set of values

You could do something like this:

update table t
    set code = concat('code-', id)
    where id in (1, 2, 3);

If the codes aren't really tied to the ids, you can use a case:

update table t
    set code = (case when id = 1 then 'code-1'
                     when id = 2 then 'code-2'
                     when id = 3 then 'code-3'
                end)
    where id in (1, 2, 3);

---

Related Topics