<co***************************@yahoo.com> wrote in message
news:bb**************************@posting.google.c om...
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.