SQL Server update a table based on values in the same table

Question

Is it possible to update a column on a table using values from the same column? The update is dependent on the rows being picked in the right order.

The query that I tried is as follows:

UPDATE myTable
SET language = (SELECT language FROM myTable t2
        WHERE t1.book = t2.book
        AND t1.firstRun = t2.lastRun + 1)
FROM myTable t1
WHERE language = '--'

The result is that I end up with language as NULL in a few cases.

My table looks like this:

book    | firstRun    | lastRun    | language
----------------------------------------------
b1      | 1           | 4          | English
b1      | 5           | 9          | --
b1      | 10          | 25         | French
b1      | 26          | 28         | --

Required output:

book    | firstRun    | lastRun    | language
----------------------------------------------
b1      | 1           | 4          | English
b1      | 5           | 9          | English
b1      | 10          | 25         | French
b1      | 26          | 28         | French

It is possible, and your update statement seems ok to me. You are probably getting nulls in rows that have no match for the subquery. — Zohar Peled
– Zohar Peled, Commented Aug 6, 2015 at 19:58
Your update query seems to run fine like @ZoharPeled said. sqlfiddle.com/#!3/e0194/1/0 — FutbolFan
– FutbolFan, Commented Aug 6, 2015 at 20:07
Thank you for your response. It works for a small toy table like this. But I find that on my larger table, where the order of the rows may be different, it does not update the rows correctly. Some rows are left as '--' — ame
– ame, Commented Aug 6, 2015 at 20:21

HansLindgren · Accepted Answer · 2015-08-07 09:12:18Z

1

Can you include some result rows where language ends up as NULL? And also how those rows looked like before?

To end up as NULL, the JOIN, in the subquery, would have to produce 0 rows or that there already are rows in 'myTable' where language IS NULL.

P.S. Easier when you indent and clean your code:

UPDATE 
    myTable
SET 
    myTable.language = t2.language 
FROM 
    myTable t2
WHERE 
    myTable.language    = '--' AND
    myTable.book        = t2.book AND
    myTable.firstRun    = t2.lastRun + 1

edited Aug 7, 2015 at 9:12

answered Aug 7, 2015 at 9:06

HansLindgren

3592 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server update a table based on values in the same table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related