bbruman bbruman - 1 month ago 5
SQL Question

Product Variant Affecting all Rows with Keyword in MySQL

I am trying to update table inventories across two (

inventory
,
sold
) MySQL tables.

Let's say the sku we are dealing with is
BT888-16


UPDATE inventory JOIN sold
ON inventory.sku = sold.sku
SET inventory.quantity = inventory.quantity - sold.quantity


This works as expected, but
BT888-16
is a product variant for the
BT888
sku.

How would I make it so that
BT888-16
in
sold
affects all quantity rows with
BT888
in
inventory
?

For example,
BT888-16
should affect all quantity rows with the phrase
BT888
in it?
BT888
BT888-01
BT888-02
BT888-03
-- all the way up to
BT888-***

Answer

This seems kludgy, but you can put the logic in explicitly:

UPDATE inventory i JOIN
       sold s
       ON i.sku = s.sku or
          (s.sku = 'BT888-16' and i.sku like 'BT888-%')
    SET i.quantity = i.quantity - s.quantity;

EDIT:

I mean, you can start playing games like this:

UPDATE inventory i JOIN
       sold s
       ON i.sku = s.sku or
          (s.sku like '%-16' and i.sku like concat(substring_index(s.sku, '-', 1), '-%')
    SET i.quantity = i.quantity - s.quantity;

However, I think you need a column in a table that does the mapping for you. That is, you need to be able to define groups of SKU ids that are "mutually" affected.

Comments