CODEWITHSUNDEEP
sqlsql-server
vibhav bhavsar
vibhav bhavsar

Reputation: 197

Update table based on range value store in another table

I have one table A which contain data as below

From    To      value
---------------------
0       0.15    0.45
0.16    0.25    0.85
0.26    0.35    0.95

I have another table B which contain million of rows and would like to prepare update query to get value from table A where table B value between From and To column values from table A.

I have tried to create a query to select and update, here is select statement

SELECT
    B.value
    , (
          SELECT
              shipmentcost
          FROM
              #tmp A
          WHERE
              B.value BETWEEN A.Fromlb AND A.tolb
      ) * B.value AS finalvalue
FROM
    tbl B

Advice best alternative way.

Upvotes: 0

Views: 528

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You would use join:

update b
    set b.finalvalue = a.shipmentcost * b.value 
    from b join
         a
         on b.value between a.from and t.to;

Notes:

  • from and to are really bad names for columns because they are SQL keywords.
  • This query will take a long time, because it takes time to update a bunch of rows.
  • You might consider not storing the value but calculating it when you need it.

Upvotes: 1

Related Questions