By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,070 Members | 1,238 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,070 IT Pros & Developers. It's quick & easy.

resultsets - deleting from while processing safe?

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a

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

P: n/a

"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.