CODEWITHSUNDEEP
mysqlsqlsql-server
user3437721
user3437721

Reputation: 2299

SQL loop to populate values using value from another table

So given the table structure of 2 tables:

line_item

line_item_id  item_id   a_basic   a_original    b_basic      b_original    c_basic      c_original
1               1         NULL        5          NULL            2           NULL           1
2               1         NULL        6          NULL            100         NULL           5
3               2         NULL        10         NULL            50          NULL           15
4               2         NULL        2          NULL            12          NULL           100

item

item_id   rate
1          1.5
2          2

I need to populate the null values in the line_item table using this calculation:

item.rate x x_original

so for line_item_id: 1, it would be 

a_original x item_id.rate (5 x 1.5)

Is this possible in a SQL script that supports sql server and mysql?

Note that we cannot use views or computed columns as a requirement.

Upvotes: 0

Views: 77

Answers (1)

Andrey Korneyev
Andrey Korneyev

Reputation: 26876

You don't need any loop here. Your goal can be achieved by simple update.

But since update join syntax is different in MySQL and SQL Server, you can't do it by some "universal" query.

In SQL Server it should be 

update LI set
    a_basic = coalesce(a_basic, LI.a_original * I.rate),
    b_basic = coalesce(b_basic, LI.b_original * I.rate),
    c_basic = coalesce(c_basic, LI.c_original * I.rate)
from line_item as LI
    left outer join item as I on I.item_id = LI.item_id

And in MySQL it should be

update line_item as LI
    left outer join item as I on I.item_id = LI.item_id
set
    a_basic = coalesce(a_basic, LI.a_original * I.rate),
    b_basic = coalesce(b_basic, LI.b_original * I.rate),
    c_basic = coalesce(c_basic, LI.c_original * I.rate)

Upvotes: 2

Related Questions