Update rows returned by a SELECT statement

Question

I have a complex(*) SQL SELECT statement that returns the rows I require to be updated

How do I update the rows returned by my SELECT statement only?

All the posts I see about updating rows using a SELECT statement talk about INNER JOIN against another table using fields to match (How do I UPDATE from a SELECT in SQL Server?)

My statement is

SELECT * FROM table1 I 
INNER JOIN
(SELECT *
  FROM (
                SELECT  INTNO,EFFDTE,
                        ROW_NUMBER() OVER(PARTITION BY INTNO,EFFDTE ORDER BY EFFDTE DESC) rn
                    FROM table2 WHERE REFID = 8888 AND EFFDTE IS NOT NULL AND INTNO <> 1234567
              ) a
WHERE rn = 1) X
ON X.INTNO = I.MEMBNO AND I.ENDDTE = DATEADD(DD,-1,X.EFFDTE)
WHERE I.ENDRSN = 'abc'

score 1 · Answer 1 · answered Jan 12 '15 at 09:01

Use this syntax

UPDATE a
SET    a.col1 = b.col1,
       a.col1 = b.col2
       .....
       .....
FROM   update_table a
       JOIN(SELECT *
            FROM   table1 I
                   INNER JOIN (SELECT INTNO,
                                      EFFDTE,
                                      Row_number()
                                        OVER(
                                          PARTITION BY INTNO, EFFDTE
                                          ORDER BY EFFDTE DESC) rn
                               FROM   table2
                               WHERE  REFID = 8888
                                      AND EFFDTE IS NOT NULL
                                      AND INTNO <> 1234567) X
                           ON X.INTNO = I.MEMBNO
                              AND I.ENDDTE = Dateadd(DD, -1, X.EFFDTE)
            WHERE  I.ENDRSN = 'abc'
                   AND rn = 1) b
         ON a.common_colum = b.common_colum

Thanks. What happens if I do not have a common column? ie: a value that only appears in my returned rows from my SELECT statement? My SELECT statement might return 3 of the 10 rows that have INTNO 111222 but I only want to update the 3 of them, not all 10 — Mike, Jan 12 '15 at 09:09
Thanks @NoDisplayName - I realised that my SELECT statement WAS the answer. I was trying to JOIN my entire select statement to an update whereas I needed to step back slightly and use the update against it. (I've added the answer) — Mike, Jan 12 '15 at 09:33

score 0 · Answer 2 · edited Jan 12 '15 at 09:55

0

Try this

Update table1 
set Col_Name=Value
FROM table1 I 
INNER JOIN
(SELECT *
  FROM (
                SELECT  INTNO,EFFDTE,
                        ROW_NUMBER() OVER(PARTITION BY INTNO,EFFDTE ORDER BY EFFDTE DESC) rn
                    FROM table2 WHERE REFID = 8888 AND EFFDTE IS NOT NULL AND INTNO <> 1234567
              ) a
WHERE rn = 1) X
ON X.INTNO = I.MEMBNO AND I.ENDDTE = DATEADD(DD,-1,X.EFFDTE)
WHERE I.ENDRSN = 'abc'

edited Jan 12 '15 at 09:55

David Arenburg

91,361
17
137
196

answered Jan 12 '15 at 09:12

Sushil Kr. Singh

21
6

Welcome to stack overflow , This is not an answer ,You can add this as a comment , please wit until you get at least 50 reputation from this site for commenting. – Arunprasanth K V Jan 12 '15 at 09:15
is this answer. please check – Sushil Kr. Singh Jan 12 '15 at 09:32
**Please check again, i putted some text** – Sushil Kr. Singh Jan 12 '15 at 09:34
Ok, I have a doubt. What you mean by value from ? – Arunprasanth K V Jan 12 '15 at 09:36
yes look at the answer `Update table1 set Col_Name=Value FROM table1 I INNER JOIN (SELECT * FROM ( SELECT INTNO,EFFDTE, ROW_NUMBER() OVER(PARTITION BY INTNO,EFFDTE ORDER BY EFFDTE DESC) rn FROM table2 WHERE REFID = 8888 AND EFFDTE IS NOT NULL AND INTNO <> 1234567 ) a WHERE rn = 1) X ON X.INTNO = I.MEMBNO AND I.ENDDTE = DATEADD(DD,-1,X.EFFDTE) WHERE I.ENDRSN = 'abc'` – Sushil Kr. Singh Jan 12 '15 at 09:44

score 0 · Answer 3 · answered Jan 12 '15 at 09:35

It was a matter of approach and understanding how to best approach SQL queries.

I do not need to join to the entire SELECT I had built, I just need to replace the SELECT with the UPDATE target so that the join against the update uses the logic (and common columns)

UPDATE table1
SET field1 = 'MP', field2 = NULL
FROM 
table1 I JOIN
(SELECT *
  FROM (
                SELECT  INTNO,EFFDTE,
                        ROW_NUMBER() OVER(PARTITION BY INTNO,EFFDTE ORDER BY EFFDTE DESC) rn
                    FROM table2 WHERE REFID = 8888 AND EFFDTE IS NOT NULL AND INTNO <> 1234567
              ) a
WHERE rn = 1) X
ON X.INTNO = I.MEMBNO AND I.ENDDTE = DATEADD(DD,-1,X.EFFDTE)
WHERE I.ENDRSN = 'abc'

Update rows returned by a SELECT statement

3 Answers3