CODEWITHSUNDEEP
sqlsql-server
James123
James123

Reputation: 11672

Update null in column with existing value from a column

By seeing below example, how can I update ORIGINAL_ORDER_NO null value with an already existing value (ex with: 9007853627), and also update original_order_line_no number with equivalent ORDER_LINE_NO?

I will pass the ORDER_NO and script needs to updates rest of it.

ORIGINAL_ORDER_NO   original_order_line_no  ORDER_NO    ORDER_LINE_NO   
---------------------------------------------------------------------
9007853627             1                    9008142190   1              
9007853627             2                    9008142190   2  
NULL                  NULL                  9008142190   4   
NULL                  NULL                  9008142190   5

Desired output:

 ORIGINAL_ORDER_NO  original_order_line_no  ORDER_NO    ORDER_LINE_NO   
-----------------------------------------------------------------------
    9007853627             1                9008142190   1              
    9007853627             2                9008142190   2  
    9007853627             4                9008142190   4   
    9007853627             5                9008142190   5

Upvotes: 0

Views: 57

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 176314

You could use:

WITH cte AS (
  SELECT *, MIN(ORIGINAL_ORDER_NO) OVER(PARTITION BY ORDER_NO) as s
  FROM tab
  WHERE order_no = ?
)
UPDATE cte
SET ORIGINAL_ORDER_NO = s
   ,original_order_line_no = ORDER_LINE_NO  
WHERE ORIGINAL_ORDER_NO IS NULL;

DBFiddle Demo

Upvotes: 1

S3S
S3S

Reputation: 25152

ISNULL is one way, and @yourParam here is what you are passing in for ORDER_NO

update yourTable
set   original_order_line_no   = isnull(original_order_line_no,ORDER_LINE_NO)
     ,ORIGINAL_ORDER_NO   = (select top 1 ORIGINAL_ORDER_NO from yourTable where ORDER_NO = @yourParam)  
where ORDER_NO = @yourParam and ORIGINAL_ORDER_NO is null

Upvotes: 3

Related Questions