[RESOLVED] DELETE question

182x

Hey guys,

I am going to write a php script to delete some records based on values supplies by the user. I was just wondering if the query is correct but there are no records to delete for example if this query was executed but there was no testId 7, would an error occur or would the query be performed but just not delete any records?

Thanks. (not at a testing machine at the mo or would test this out myself)

$testId = 7;
$q="$q="DELETE  FROM test WHERE testId = $testId";
mysql_query($q, $link_id)or die(mysql_error());

rulian

it's actually

$testId = 7;
$q="DELETE FROM user WHERE testId = $testId";
mysql_query($q, $link_id)or die(mysql_error()); 

or $q="DELETE FROM user WHERE testId = $testId LIMIT 1";

just to make sure you delete only 1
if nothing matches it will simply return false, not errror

Sxooter

rulian wrote:

it's actually

$testId = 7;
$q="DELETE FROM user WHERE testId = $testId";
mysql_query($q, $link_id)or die(mysql_error()); 

or $q="DELETE FROM user WHERE testId = $testId LIMIT 1";

just to make sure you delete only 1
if nothing matches it will simply return false, not errror

Why would you want to limit it to 1 in this situation? I'm not sure it was required by the OP. btw, delete with a limit clause is a mysqlism, don't expect it to work on other databases.

182x: You can delete with no matches and you will not get an error:

mysql> delete from demo where id = 1;
Query OK, 1 row affected (0.02 sec)
mysql> delete from demo where id = 1;
Query OK, 0 rows affected (0.00 sec)

rulian

I've used it as a safe keep, in case somethign happens where it will delete more then 1 row, if you want to only delete a single row a limit 1 will delete only one, no guaranteee it's the right one, but it wont do irreversable damage, example

DELETE from boo WHERE blah = 'crap' LIMIT 1

will only delete id 1
granted, you should delete specific rows by their uniq keys or mutiple wheres, but it's still a safe keep to have LIMIT 1