delete or update depending column value

Question

Suppose, I have a product table. It has product_id and product_name columns. and I've a table product_purchase

pr_pur_id  product_id(FK)  pur_quantity
 1           170           6
 2           190           6
 3           270           0
 4           70            1
 5           10            6
 6           20            6

and I've a product_sale table. The Table has pr_sale_id , product_id(FK) and sale_quantity columns.

If I want to insert product_sale which product_id has already in product_purchase table, the product_purchase.pr_pur_id should delete if product_purchase.pur_quantity is 0. If the product_purchase.pur_quantity is more then 0 it will reduce depends on product_sale.sale_quanity.

How can I execute update or delete query depends on product_purchase.pur_quantity column?

You'll have to do that in your server-side (or client-side) code. As far as I know, there is no way to force mySQL to decide to perform an `UPDATE` or `DELETE`. — DevlshOne, Jul 26 '13 at 18:37
you can write a stored proc that check value and based on that takes decision to fire and update or delete query. — Guanxi, Jul 26 '13 at 18:39
I'm not sure I understand you, but I suspect either the `REPLACE` or `INSERT ... ON DUPLICATE KEY UPDATE` statements are what you're looking for. — Barmar, Jul 26 '13 at 18:39
It would help if you showed examples of of what you want to happen in various cases. — Barmar, Jul 26 '13 at 18:40
I agree with Barman, you probably can do the work with insert on duplicate key statment — CaveCoder, Jul 26 '13 at 18:40
@Barmar . why I'll use REPLACE or INSERT ... ON DUPLICATE KEY UPDATE. . I need remove product in purchase list when it sell — Imran, Jul 26 '13 at 19:14
Oh, I see. Every time you make a sale, you reduce the quantity in inventory, and when quantity goes to 0 you want to delete the row. I think the best way to do this is with a trigger. — Barmar, Jul 26 '13 at 19:17
Neither am I, sorry. Why don't you try to figure it out by reading the documentation? — Barmar, Jul 26 '13 at 19:20

score 0 · Accepted Answer · answered Jul 27 '13 at 10:33

0

Have you considered simply leaving the row in there with a 0 available count? Then it acts as a historical record if you ever need to refer to this information later. The only real downside is that you'll need to filter those rows out in queries with a clause like WHERE pur_quantity != 0.

answered Jul 27 '13 at 10:33

jpmc26

28,463
14
94
146

but extra records which pur_quanity=0 wiil never need in next time. why I'll keep junk records? will it disturb my database performance when extra record will big amount. – Imran Jul 27 '13 at 11:19
Are they junk records? You'll never, ever, ever want to know the history of your available products? How many rows are we talking about that it would affect performance noticeably? Even if there are a large number of records, I imagine that you could overcome this with good indexing. – jpmc26 Jul 27 '13 at 19:19
ok sir, I understand and little bit worry.If you're right. my database should change. product_sale table column should pr_sale_id , product_pur_id(FK) and sale_quantity.(not product_id). and oh this software is for a big shoe shop. they have at least 5-10 thousands diffrent products. For purchase product report, my idea was collect from sale table. may be that was not good idea. thank you. – Imran Jul 27 '13 at 19:48
Only you can determine that based on the needs of your client and the application. So maybe deleting them is the right thing to do; that's for your to determine. I was just pointing out that there's often value in keeping historical information, especially if the cost of doing so is small. On a side note, 5-10 thousand rows doesn't sound like a lot for a relational database. See this question for some more in depth information about what's "a lot" for a relational database: http://stackoverflow.com/questions/5350581/how-many-rows-are-too-many-for-a-mysql-table. – jpmc26 Jul 27 '13 at 21:53

delete or update depending column value

1 Answers1