how can I compare two table and delete some data in mysql procedures?

Question

0.00/5 (No votes)

See more: (untagged)

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???

Posted 12-Feb-15 1:55am

Member-2338430

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2015-02-12T02:03:00

Solution 1

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

An example:

SQL

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 )

Posted 12-Feb-15 2:03am

CHill60

Updated 12-Feb-15 2:15am

v2

Comments

Member-2338430 12-Feb-15 8:38am

I am trying...

Member-2338430 12-Feb-15 17:31pm

giving error this =you cant specify target table [customer basket]for update in from clause

Member-2338430 12-Feb-15 19:53pm

thanks I solved problem

Member-2338430 13-Feb-15 3:39am

another question like this
there are two table same as;
pay table has product_id and how many product added so: number
product table same above product_id and stock
for ex X product stock=10 I paid or added 2 so product table stock must be update new value 8.
I tryed like above but I cant
update [products] set Stock=Stock-number
WHERE products_id IN (
SELECT cb.products_id,number FROM [pay] cb
INNER JOIN [products] p ON cb.products_id = p.products_id
WHERE cb.situation=1)
situation=1 means paid

CHill60 17-Feb-15 11:32am

You can't select more than one column in the inner select - the one you used for the IN ... if you think about it what would sql compare the products_id to ... the cb.products_id or the number?