如何在mysql程序中比较两个表并删除一些数据？ [英] how can I compare two table and delete some data in mysql procedures?

问题描述

我的mysql数据库中有两个表。

一个名为products的表有products_id和stock

另一个表客户购物篮产品_id



为前客户添加了3件商品



我想在商店购买的商品



去客户表获取产品_id一个一个



获得第一个product_id并将产品表与producs_id进行比较，如果库存为0，我将查看库存删除客户购物篮表这个产品_id



继续第二，第三......



所以它可以循环???

there is two table in my mysql database.
one table called products has products_id and stock
another table customer basket products_id

for ex customer added 3 products

I want to do in store procedure

go to customer table get products_id one by one

get first product_id and compare products table with producs_id and I will look stock if stock is 0 delete customer basket table for this products_id

continue second,third...

so it can be loop???

推荐答案

否 - 不要使用循环。 了解加入 [ ^ ]



一个例子：

No - don't use a loop. Learn about JOIN[^]

An example:

DELETE FROM [customer basket]
WHERE products_id IN (
    SELECT cb.products_id FROM [customer basket] cb
    INNER JOIN [products] p ON cb.products_id = p.products_id
    WHERE p.stock = 0 )

这篇关于如何在mysql程序中比较两个表并删除一些数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！