CODEWITHSUNDEEP
sql-server
user3569267
user3569267

Reputation: 1135

SQL Server Update clause

I have 2 tables: t1 and t2 created as following:

create table t1(tshipping_id int,TShipping_POD bit,TShipping_Tracking2 varchar(10),TShipping_Weight int,TSipphing_LastTrackingDate datetime)

create table t2(tshipping_id int,POD bit,Tracking2 varchar(10),Weight int)

I want to update t1 with the value of t2 for the row having the same tshipping_ID

UPDATE  t1
SET     TSipphing_LastTrackingDate = GetDate(),
        TShipping_Tracking2 = t2.Tracking2,
        TShipping_POD = t2.POD          
from    t1
inner join t2 on t1.tshipping_id=t2.tshipping_id

and I want also to update, in the same query, t1.TShipping_Weight with the value of t2.weight only if t1.TShipping_weight=0 or t1.TShipping_weight is null

Can anyone have an optimized solution?

Thanks

Upvotes: 0

Views: 22

Answers (2)

SeraphimFoA
SeraphimFoA

Reputation: 466

try this one. I don't remember if sql-server syntax is the same as oracle, so you need to try

update 
  table1 t1
set
  (
    t1.TSipphing_LastTrackingDate 
    t1.TShipping_Tracking2 
    t1.TShipping_Weight
    t1.TShipping_POD    
      ) = (
    select
      GetDate(),
      t2.Tracking2,
      CASE WHEN (t1.TShipping_weight=0 or t1.TShipping_weight is null) THEN  t2.weight  ELSE  t1.TShipping_weight END,
      t2.POD  
    from
      table2  t2
    where
      t1.tshipping_id=t2.tshipping_id
     );

Upvotes: 0

Jesuraja
Jesuraja

Reputation: 3844

Try this:

UPDATE  t1
SET     TSipphing_LastTrackingDate = GetDate(),
        TShipping_Tracking2 = t2.Tracking2,
        TShipping_POD = t2.POD,
        TShipping_Weight = (CASE 
                                WHEN ISNULL(t1.TShipping_weight,0) = 0 THEN t2.weight          
                                ELSE TShipping_Weight
                            END)
from    t1
inner join t2 on t1.tshipping_id = t2.tshipping_id

Upvotes: 1

Related Questions