SQL Server Using UPDATE and SET in temp table but values are not updating

yc0p9oo0 于 2023-10-15 发布在其他

关注(0)|答案(2)|浏览(99)

I am using SQL Server v18.

I need to update a temp table and replace the values in Column_B that are equal to zero with the non-zero values, based on Column_A.

This is how the initial table looks like:
| Column_A | Column_B |
| ------------ | ------------ |
| ABC | 0 |
| ABC | 123 |
| ABC | 0 |
| DEF | 456 |
| DEF | 0 |
| DEF | 0 |
| GHI | 123 |
| GHI | 0 |

This is what I am expecting the final table to look like:

Column_A	Column_B
ABC	123
ABC	123
ABC	123
DEF	456
DEF	456
DEF	456
GHI	123
GHI	123

Here is the code that I have tried so far.

UPDATE #temp_table
SET Column_B = CASE 
                   WHEN EXISTS (SELECT *
                                FROM #temp_table AS t1
                                WHERE Column_B <> 0
                                  AND Column_A = t1.Column_A)
                       THEN Column_B 
                       ELSE 0 
               END
WHERE Column_B = 0;

This is the message from the above code:

5 rows affected

However, when I validate the results, the table remains unchanged. The non-zero values have not been replaced.

I will appreciate any help. Thanks.

sql-server

来源：https://stackoverflow.com/questions/77114810/using-update-and-set-in-temp-table-but-values-are-not-updating

2条答案

按热度按时间

f87krz0w1#

This answer follows the requirement you described in comments:
I will have to select the correct value, which would be the first record written, based on a DateTime column

I think you will do better to edit your question and adjust the sample data rather than writing this as a comment that people might not read.

Anyway, this query using FIRST_VALUE will fetch the first of Column B for each value in Column A which is not NULL and not 0 :

SELECT DISTINCT
  Column_A,
  FIRST_VALUE(column_B) IGNORE NULLS OVER
    (PARTITION BY column_A ORDER BY yourdate) AS updateValue
FROM t
WHERE Column_B <> 0;

The ORDER BY yourdate clause covers your requirement to take "the first record written" because this is the row having the earliest date. Above query can be used in the update command:

WITH updateValues AS
(SELECT DISTINCT
  Column_A,
  FIRST_VALUE(column_B) IGNORE NULLS OVER
    (PARTITION BY column_A ORDER BY yourdate) AS updateValue
FROM t
WHERE Column_B <> 0)
UPDATE t 
  SET t.Column_B = u.updateValue
  FROM t 
  INNER JOIN updateValues u
  ON t.Column_A = u.Column_A
  WHERE t.Column_B = 0;

The WHERE clause in the update command makes sure to update those rows only where Column B is 0 .

See this sample fiddle .

It shows the result for some data similar to yours, but extended by some other rows. Thus, it points out how this idea works.

赞(0）回复(0）举报 2023-10-15

kokeuurv2#

IF OBJECT_ID(N'#TEMP_TABLE',N'U')IS NOT NULL
   DROP TABLE TEMPDB..#TEMP_TABLE

CREATE TABLE #TEMP_TABLE
(
   Column_A CHAR(3) NOT NULL,
   Column_B SMALLINT NOT NULL
)
INSERT #TEMP_TABLE(Column_A,Column_B)
   SELECT 'ABC',0 UNION ALL
   SELECT 'ABC',    123 UNION ALL
   SELECT 'ABC',    0 UNION ALL
   SELECT 'DEF',    456 UNION ALL
   SELECT 'DEF',    0 UNION ALL
   SELECT 'DEF',    0 UNION ALL
   SELECT 'GHI',    123 UNION ALL
   SELECT 'GHI',    0

 UPDATE T SET
   T.Column_B=X.MAX_B
 FROM #TEMP_TABLE AS T
 JOIN
 (
   SELECT Z.Column_A,MAX(Z.Column_B)AS MAX_B
      FROM #TEMP_TABLE AS Z
   GROUP BY  Z.Column_A
 ) X ON T.Column_A=X.Column_A

 SELECT * FROM #TEMP_TABLE

 DROP TABLE #TEMP_TABLE;

Please try this query with correlated UPDATE (update...join)

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Using UPDATE and SET in temp table but values are not updating

2条答案

相关问题

热门标签

最新问答