Update null in column with existing value from a column

Question

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

S3S · Accepted Answer · 2018-09-04 19:35:48Z

3

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

edited Sep 4, 2018 at 19:35

answered Sep 4, 2018 at 19:22

S3S

25.2k5 gold badges29 silver badges48 bronze badges

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

1 Comment

Lukasz Szozda Over a year ago

9007853627 != 9008142190

Lukasz Szozda · Accepted Answer · 2018-09-04 19:49:39Z

1

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

edited Sep 4, 2018 at 19:49

answered Sep 4, 2018 at 19:23

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

4 Comments

James123 Over a year ago

Where can I check with ORDER_NO?

Lukasz Szozda Over a year ago

@James123 Did you even try it?

James123 Over a year ago

dbfiddle.uk/….. If you see this I don't want update all orders. I want update particular order.

Lukasz Szozda Over a year ago

@James123 Just simply add where clause demo2

Collectives™ on Stack Overflow

Update null in column with existing value from a column

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related