SQL loop to populate values using value from another table

Question

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.

coalesce(original_column, item.rate x x_original)?

jarlh
– jarlh

2016-01-21 11:15:44 +00:00
Commented Jan 21, 2016 at 11:15 — jarlh
– jarlh, Commented Jan 21, 2016 at 11:15

Andrey Korneyev · Accepted Answer · 2016-01-21 11:24:08Z

2

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)

edited Jan 21, 2016 at 11:24

answered Jan 21, 2016 at 11:15

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

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

Collectives™ on Stack Overflow

SQL loop to populate values using value from another table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related