Reasonable delete alternative to subqueries?

[compu_global_hyper_mega_net_2]

I'd like to do something like:

delete from a where id in (select * from b where pattern like
'%something%')

I may or may not have the syntax right. I know that this sort of
subquery isn't available until mysql 4.1. 4.1 beta is taking too long
to release and I need something now, so here's what I'm doing now:

replace a (id) select -refId id from b where pattern like
'%something%'

So I'm marking/flagging the rows that will need to be deleted. Next,
I delete them:

delete from a where id < 0

I've tested this, and it actually works. id is a primary key btw.

good or not good?

[Siemel Naran]

<co************ *************** @yahoo.com> wrote in message
news:bb******** *************** ***@posting.goo gle.com...

I'd like to do something like:

delete from a where id in (select * from b where pattern like
'%something%')

I may or may not have the syntax right. I know that this sort of
subquery isn't available until mysql 4.1. 4.1 beta is taking too long
to release and I need something now, so here's what I'm doing now:

replace a (id) select -refId id from b where pattern like
'%something%'

So I'm marking/flagging the rows that will need to be deleted. Next,
I delete them:

delete from a where id < 0

I've tested this, and it actually works. id is a primary key btw.

good or not good?

Right idea, but not good because it will fail when there are many
connections, each trying to delete records. You could flag the records with
the session id. To get this id, use the connection_id() function.

I wonder if we could use the temporary table extension MySql provides, along
with the update/delete with many tables idea. The temporary table is valid
for this connection session only and is visible only to this session. Give
it a shot.

create temporary table adel (
aid int(1) unsigned not null references a(id),
unique index theindex (aid)
);

insert into adel (aid)
select id from a where ...;

delete from a, inner join adel on a.id = adel.aid;

At this point, I expect adel to have zero records.

[Chris Hope]

co************* **************@ yahoo.com wrote:

I'd like to do something like:

delete from a where id in (select * from b where pattern like
'%something%')

I may or may not have the syntax right. I know that this sort of
subquery isn't available until mysql 4.1. 4.1 beta is taking too long
to release and I need something now, so here's what I'm doing now:

[snip]

This article should help you:
http://www.electrictoolbox.com/artic...-table-delete/

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[steve]

"Chris Hope" wrote:

co************* **************@ yahoo.com wrote:

I’d like to do something like:

delete from a where id in (select * from b where pattern like
’%something%’)

I may or may not have the syntax right. I know that this sort of
subquery isn’t available until mysql 4.1. 4.1 beta is taking

too long

to release and I need something now, so here’s what I’m

doing now:

[snip]

This article should help you:
http://www.electrictoolbox.com/artic...-table-delete/

Excellent, Chris. Thanks I bet 90% of us did not know how to use
this before (I did not for sure)

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-Reason...ict133954.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=450381