MySQL ODBC Connector - Semi-complicated delete

Author

Semi-complicated delete

Scott Haneda

2006-01-11, 7:23 am

4.0.18-standard-log

I have a table cart and a table products

Key is as follows:
products.id = cart.prod_id

The problem I have is we have decided to store the users cart, so when they
come back it is still in the same state they left it. Pretty usual stuff so
far.

Two things can possible happen that would make this bad:
1) product has been deleted
2) product has been disabled.

Just before I am checking the user out, I want to fix this scenario with a
delete statement. What I need to do is:

DELETE FROM cart where cart.prod_id does not exist in the products table, or
where 'online' = 0.

There is a user_id that I match on as well, but that does not entirely
matter to this question.

Deleting where online = 0 is simple, but deleting where there is a lack of a
matching product has me stumped.
--
-------------------------------------------------------------
Scott Haneda Tel: 415.898.2602
<http://www.newgeo.com> Novato, CA U.S.A.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Adrian Bruce

2006-01-11, 7:23 am

IN MySQL 5 you could use a sub query(
http://dev.mysql.com/doc/refman/5.0...subqueries.html).

However, i would try using a left join between cart and products and
then bring back the results where the products.id field is 'NULL'.
There may be a better way of doing this but that is what comes to mind
first.

Ade

Scott Haneda wrote:

>4.0.18-standard-log
>
>I have a table cart and a table products
>
>Key is as follows:
>products.id = cart.prod_id
>
>The problem I have is we have decided to store the users cart, so when they
>come back it is still in the same state they left it. Pretty usual stuff so
>far.
>
>Two things can possible happen that would make this bad:
>1) product has been deleted
>2) product has been disabled.
>
>Just before I am checking the user out, I want to fix this scenario with a
>delete statement. What I need to do is:
>
>DELETE FROM cart where cart.prod_id does not exist in the products table, or
>where 'online' = 0.
>
>There is a user_id that I match on as well, but that does not entirely
>matter to this question.
>
>Deleting where online = 0 is simple, but deleting where there is a lack of a
>matching product has me stumped.
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

sheeri kritzer

2006-01-26, 4:56 pm

The better way to do this would be to have the cart.prod_id be a
foreign key field that references products.id (available as of
3.23.44) and use "ON CASCADE DELETE". But that alters your schema,
and you want to think very hard about the problems it might create
(and do it with your data on a test server first, so you can see any
warnings or errors).

-Sheeri

On 1/11/06, Adrian Bruce <abruce@stvincent.ac.uk> wrote:
>
> IN MySQL 5 you could use a sub query(
> http://dev.mysql.com/doc/refman/5.0...subqueries.html).
>
> However, i would try using a left join between cart and products and
> then bring back the results where the products.id field is 'NULL'.
> There may be a better way of doing this but that is what comes to mind
> first.
>
> Ade
>
> Scott Haneda wrote:
>
hey[color=darkred]
f so[color=darkred]
a[color=darkred]
, or[color=darkred]
k of a[color=darkred]
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql? unsub...mail
.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

SGreen@unimin.com

2006-01-26, 4:56 pm

--=_alternative 0063DB1B85257101_=
Content-Type: text/plain; charset="US-ASCII"

Has everyone forgotten that since v4.0, MySQL has a multi-table delete
statement?

http://dev.mysql.com/doc/refman/4.1/en/delete.html

Your original query was almost it (Adrian was on the right track, too):

DELETE cart
FROM cart
LEFT JOIN products prod
WHERE prod.id is null
or prod.online=0;

Sheeri, your answer would have worked for the deleted products but not for
those product still in the database but whose online status was changed to
0.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

sheeri kritzer <awfief@gmail.com> wrote on 01/25/2006 12:47:08 PM:

> The better way to do this would be to have the cart.prod_id be a
> foreign key field that references products.id (available as of
> 3.23.44) and use "ON CASCADE DELETE". But that alters your schema,
> and you want to think very hard about the problems it might create
> (and do it with your data on a test server first, so you can see any
> warnings or errors).
>
> -Sheeri
>
> On 1/11/06, Adrian Bruce <abruce@stvincent.ac.uk> wrote:
they[color=darkred]
> usual stuff so
with a[color=darkred]
> products table, or
entirely[color=darkr
ed]
> a lack of a
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=sgreen@unimin.com
>

--=_alternative 0063DB1B85257101_=--

Sponsored Links