CODEWITHSUNDEEP
sql
LOL
LOL

Reputation: 111

sql server update a table with data from another table

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

Upvotes: 0

Views: 1045

Answers (4)

KAmeer
KAmeer

Reputation: 21

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

Upvotes: 0

Justin Donohoo
Justin Donohoo

Reputation: 380

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

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

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.

Upvotes: 1

TechDo
TechDo

Reputation: 18629

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

Upvotes: 1

Related Questions