467,228 Members | 1,433 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,228 developers. It's quick & easy.

resultsets - deleting from while processing safe?

Is it safe to do what is below? deleting from a resultset while you are
processing it? I don't know how dynamic the SQL database is. I assume you
get a cursor to live dataset. Even if it is a cursor as opposed to a table,
will the cursor's integrity be ruined if the current record is deleted?

$q2=mysql_query("SELECT * FROM table1 WHERE id1=5", $link);
while ($row2=mysql_fetch_array($q2)) {
//do some stuff with resultset
mysql_query("DELETE FROM table1 WHERE id1=5 AND id2=$row2[id2]", $link);
}
mysql_free_result($q2);


Mar 18 '06 #1
  • viewed: 1385
Share:
4 Replies
Jim Michaels wrote:
Is it safe to do what is below? deleting from a resultset while you are
processing it? I don't know how dynamic the SQL database is. I assume you
get a cursor to live dataset. Even if it is a cursor as opposed to a table,
will the cursor's integrity be ruined if the current record is deleted?

$q2=mysql_query("SELECT * FROM table1 WHERE id1=5", $link);
while ($row2=mysql_fetch_array($q2)) {
//do some stuff with resultset
mysql_query("DELETE FROM table1 WHERE id1=5 AND id2=$row2[id2]", $link);
}
mysql_free_result($q2);


AFAIK, it'll be totally fine. But isn't your code equivalent to this?

$q2=mysql_query("SELECT * FROM table1 WHERE id1=5", $link);
while ($row2=mysql_fetch_array($q2)) {
//do some stuff with resultset
}
mysql_free_result($q2);
mysql_query("DELETE FROM table1 WHERE id1=5", $link);

Cheers,
Nicholas Sherlock

--
http://www.sherlocksoftware.org
Mar 18 '06 #2
Nicholas Sherlock wrote:
Jim Michaels wrote:
Is it safe to do what is below? deleting from a resultset while you
are processing it? I don't know how dynamic the SQL database is. I
assume you get a cursor to live dataset. Even if it is a cursor as
opposed to a table, will the cursor's integrity be ruined if the
current record is deleted?

$q2=mysql_query("SELECT * FROM table1 WHERE id1=5", $link);
while ($row2=mysql_fetch_array($q2)) {
//do some stuff with resultset
mysql_query("DELETE FROM table1 WHERE id1=5 AND id2=$row2[id2]",
$link);
}
mysql_free_result($q2);

AFAIK, it'll be totally fine. But isn't your code equivalent to this?

$q2=mysql_query("SELECT * FROM table1 WHERE id1=5", $link);
while ($row2=mysql_fetch_array($q2)) {
//do some stuff with resultset
}
mysql_free_result($q2);
mysql_query("DELETE FROM table1 WHERE id1=5", $link);

Cheers,
Nicholas Sherlock


Jim,

I'm not sure if it's safe or not - typically I've tried to stay away
from this construct. And I haven't been able to find any doc talking
about it one way or the other.

If I were doing it, I'd save the id's in an array and delete them after
I'm done with the result set. But that's just me.

And Nicholas, no, it's not the same. Jim is deleting from the result
set (potentially multiple rows based on other selection criteria).
You're deleting all rows with id1=5, which may or may not be what he
wants (he may only want to delete some of the rows, for instance).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Mar 18 '06 #3

Jim Michaels wrote:
Is it safe to do what is below? deleting from a resultset while you are
processing it? I don't know how dynamic the SQL database is. I assume you
get a cursor to live dataset. Even if it is a cursor as opposed to a table,
will the cursor's integrity be ruined if the current record is deleted?

$q2=mysql_query("SELECT * FROM table1 WHERE id1=5", $link);
while ($row2=mysql_fetch_array($q2)) {
//do some stuff with resultset
mysql_query("DELETE FROM table1 WHERE id1=5 AND id2=$row2[id2]", $link);
}
mysql_free_result($q2);


With MySQL I believe it's safe, as the client buffers the whole result
set. It's more efficient though to save the ids into an array, then
delete all the applicable rows in a single operation with a "id2 IN (
.... )" condition.

Mar 18 '06 #4

"Chung Leong" <ch***********@hotmail.com> wrote in message
news:11**********************@z34g2000cwc.googlegr oups.com...

Jim Michaels wrote:
Is it safe to do what is below? deleting from a resultset while you are
processing it? I don't know how dynamic the SQL database is. I assume
you
get a cursor to live dataset. Even if it is a cursor as opposed to a
table,
will the cursor's integrity be ruined if the current record is deleted?

$q2=mysql_query("SELECT * FROM table1 WHERE id1=5", $link);
while ($row2=mysql_fetch_array($q2)) {
//do some stuff with resultset
mysql_query("DELETE FROM table1 WHERE id1=5 AND id2=$row2[id2]",
$link);
}
mysql_free_result($q2);


With MySQL I believe it's safe, as the client buffers the whole result
set. It's more efficient though to save the ids into an array, then
delete all the applicable rows in a single operation with a "id2 IN (
... )" condition.


thx for the tip. Hadn't thought of that. Makes sense.
Mar 19 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Tor Heigre | last post: by
10 posts views Thread by =?Utf-8?B?UGV0ZXI=?= | last post: by
reply views Thread by Adict | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.