CODEWITHSUNDEEP
mysqlsql
Petrov
Petrov

Reputation: 4250

Complex SQL Update Statement

I've got two MYSQL tables "shirts_in_box", "trousers_in_box" which represent two different classes of objects that can be in the same box : shirts and trousers, and the quantities of each box that is present.

The table "shirts_in_box" has columns "shirt_id, quantity" and "trousers_in_box" has columns "trouser_id, quantity"

I've got to deal with the following situation :

1) i start with two items in box_id = "foo" : e.g table "shirts_to_box" has a row 

shirt_id = 'xxx', quantity : '1'

and "trousers_to_box" has a row

trouser_id = 'www', quantity : '2'

2) an update list of items for box_id = 'foo' is submitted, which looks like

trousers_to_box :

box_id : foo, trousers_id : 'aaa', quantity : '5'

shirts_to_box :

box_id : foo, shirts_id : 'xxx', quantity : '2'

3) i should take this and update "shirts_to_box", "trousers_to_box" so that the above are ALL the entries in the two tables having box_id = 'foo', ie

shirts_to_box :

UPDATE shirt_id = 'xxx', quantity : '1' to quantity = 2

trousers_to_box :

ADD trouser_id = 'aaa', quantity : '5'
DELETE trouser_id = 'www', quantity : '2'

At the moment, my method is not very efficient. I make two separate queries on the two separate tables, and make my adds/updates/deletions to each separately.

Is there a way to do this all in one query ? Or even a more efficient way than what I've got at the moment ?

Upvotes: 0

Views: 137

Answers (1)

zessx
zessx

Reputation: 68818

You can use a general table item_in_box, with distinct id for all items (you musn't have the same id for a trouser and a shirt) :

-----------------------------------------------
 BOX_ID  |  ITEM_ID  |  ITEM_TYPE  |  QUANTITY
-----------------------------------------------
 foo     |  xxx      |  shirt      |  1
 foo     |  www      |  trouser    |  2
-----------------------------------------------

Then when you've got an update for the foo box, you just have to use this kind of request :

UPDATE item_to_box
SET `quantity` = CASE `item_id`
    WHEN xxx THEN 5
    WHEN yyy THEN 2
END

The new column item_type is not used in your updates, but will be helpful to differentiate your items.

EDIT

To fully generalize your code, here's what I suggest to you : enter image description here

Keep your tables shirt and trouser, but use an intermediate : item. You'll now have a single UPDATE request to do, whatever the number of item types you've got (the same one I wrote above).

Note : the item.type column is optional, you can use it to quickly know the type of an item, without querying all child tables (trouser, shirt...)

Upvotes: 1

Related Questions