sql server update a table with data from another table

Question

I have two tables source_table and destination_table

The source table is given below

orderid orderno invoiceno amount
 1         10       NULL    100
 2         NULL     11      200

i need to update destination_table with columns

orderid  orderno amount
  1         -      -
  2         -      -
  3         -      -

based on conditions , orderid of source matches with destination orderid

if source orderno value is not null then that value should be updated in the orderno column of destination , else source invoiceno should be updated in destination orderno column

TechDo · Accepted Answer · 2013-01-31 07:12:28Z

1

Please try:

Update D
    SET orderno = (case when S.orderno is not null then orderno else s.invoiceno end)
FROM
    source_table S Join destination_table D
    ON S.orderid = D.orderid

answered Jan 31, 2013 at 7:12

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

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

Comments

sgeddes · Accepted Answer · 2013-01-31 07:12:32Z

1

You have several options. One option is to use CASE:

UPDATE d
SET d.OrderNo = 
   CASE 
     WHEN S.OrderNo IS NOT NULL THEN s.OrderNo END ELSE S.invoiceno END
FROM Source S 
    JOIN Destination D ON S.OrderId = D.OrderId

Or I personally prefer to use COALESCE:

UPDATE d
SET d.OrderNo = COALESCE(S.OrderNo, S.InvoiceNo)
FROM Source S 
    JOIN Destination D ON S.OrderId = D.OrderId

Good luck.

answered Jan 31, 2013 at 7:12

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Comments

Justin Donohoo · Accepted Answer · 2013-01-31 07:12:40Z

0

Update D
SET orderno = S.orderno, amount = S.amount
FROM
source_table S Join destination_table D
ON S.orderid = D.orderid
WHERE S.orderno is not null

Update D
SET orderno = S.invoiceno, amount = S.amount
FROM
source_table S Join destination_table D
ON S.orderid = D.orderid
WHERE S.orderno is null

edited Jan 31, 2013 at 7:12

answered Jan 31, 2013 at 7:06

Justin Donohoo

3802 silver badges14 bronze badges

Comments

Oleksandr Fedorenko · Accepted Answer · 2013-01-31 07:33:20Z

0

If you are using sqlserver then you can also use ISNULL function. See below sql.

UPDATE D 
SET orderno = ISNULL(S.orderno, S.invoiceno), 
    amount =  S.amount 
FROM source_table S INNER JOIN destination_table D ON S.orderid = D.orderid 
WHERE S.orderno IS NOT NULL OR S.invoiceno IS NOT NULL

edited Jan 31, 2013 at 7:33

Oleksandr Fedorenko

17k6 gold badges42 silver badges51 bronze badges

answered Jan 31, 2013 at 7:26

KAmeer

211 gold badge1 silver badge3 bronze badges

Collectives™ on Stack Overflow

sql server update a table with data from another table

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related