MySQL Forums
Forum List  »  General

Tricky Update Query
Posted by: mike
Date: May 15, 2005 10:43AM

I am trying to do the following:

Instead of deleting a row, I will mark it as deleted by setting the column Deleted=1 and clearing all the other column data. Then when I need to insert a new row I can reuse the auto inc value for the new row from a row that was deleted. In the case that no Deleted rows exist I can just do an Insert. I am doing this so that I can easily cache all rows in this table in an array in my code, and grab it my just accessing the array using FileArray[FileID].

This should work but when I try to execute the query in the query browser it gives an error, which seems to be related to having two statements to execute at once.

SELECT @fid:=FileID FROM files WHERE Deleted=1 Limit 1;
UPDATE files SET FileName='New File', Deleted=0 WHERE FileID=@fid;

Is there a better way to reuse Auto Inc ID's?

Thanks...

Options: ReplyQuote


Subject
Written By
Posted
Tricky Update Query
May 15, 2005 10:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.