I’m wondering what the best approach is regarding a simple table table I maintain for clientCatagories (listID, cliCat, cliID). When a client changes the Categories they belong to - what is the best approach wrt MySQL? To DELETE all entries for that client and then INSERT the new category references OR REPLACE existing values and INSERT any additional references?
DELETE?INSERT seems on the face of it to be the cleanest approach but I’m concerned about referential integrity etc as suggested here: https://stackoverflow.com/questions/4854863/mysql-update-vs-insert-and-delete#4855066
If REPLACE is the better approach, what would the query look like? As I am not using the Primary key as a reference ON_DUPLICATE _KEY does not apply.
I think the advantage of using UPDATE over DELETE and INSERT is that you retain the same ID or primary key, so you don’t need to also update other scripts or tables that reference it.
But I guess it depends on the context of your DB set up and what your scripts are doing.
look at the table in this example
the PK is a surrogate key and i’ll bet a beer that no FKs to it exist
and besides, even if they did, that`s what ON UPDATE CASCADE is for
dear Reggie, i hate stackoverflow, so hard to grok
there is no issue with transactions with DELETE those followed by INSERT these
1 Like
@SamA74 - Thanks for the reply. As I say, its a simple reference table so no other tables reference it.
@r937 - Thanks for the voice of experience. I think I’ll stick to transaction Delete and then Insert…That way I can be sure of a clean slate every time. Ditto on Stack…Not a great lover of it either… Thats why I come here. There’s far less intellectual snobbery and people are more willing to help rather than simple criticise. Thanks again
why transaction?
@r937 - I was thinking PDO transaction to ensure that it’ll roll back in the event of any unforeseen issues. Or is your question a leading one r937? Are you perhaps suggesting there’s a better, more simple approach?
when you do a single sql statement, it either works or it doesn’t
if it doesn’t, it gets rolled back automatically
consider two scenarios –
A1 DELETE
A2 INSERT
B1 start stransaction
B2 DELETE
B3 INSERT
B4 commit
please explain why the transaction is necessary
@r937 Are you sure your not a school teacher…?!
C1 - I want to make sure that the deletes complete successfully before I insert new values.
In a multi-choice scenario - If in doubt, pick ‘C’. ‘C’ is always the right answer.
here’s how you do that… just run the DELETE statement
if it doesn’t work, it gets rolled back and you get an error message to that effect
if it does work… then just run the INSERT
note that you do them separately, one after the other
surely your front end application (php?) has error-checking to ensure every sql statement you issue is checked to see if it worked, yes?
@r937 Yes, my front end application does have error checking but stop calling me Surely.
so you still wanna use a transaction or nah?
@r937 I’m happy with running a delete query, getting a positive response and then running the insert. In fact that’s how I did it in a previous piece of function. My concern was, data integrity and fragmentation but as you say - if I’m not using the primary key of other updates etc then there should not be a problem. So, in short the answer is nah.
Why discourage transaction? The transaction is necessary in case the delete completes but the insert does not due to some error or accident. Without the transaction all the records are lost - with the transaction the records remain there because the delete is simply rolled back. So in this case the transaction prevents data loss - I’d say use transaction all the way!
i think you missed the part where the DELETEs somehow don’t work and the application layer detects that before issuing the INSERT
I’ve certainly missed some part of your reasoning - in our case the DELETEs come first so if they somehow don’t work the application layer detects the error and aborts the whole operation and the INSERTs won’t happen. In such a scenario it doesn’t matter if there is a transaction or not because the application will abort in either case - so I don’t know why you raised this point?
The transaction is intended for cases when the DELETEs work but the INSERTs fail - it will guard against data loss.
if the DELETE doesn’t work, just run it again
a DELETE that attempts to delete rows and then fails means the rows are still there
so run it again
if you’re paranoid, you might even keep a counter in the app to see if you tried like three times to delete, and it keeps failing…
by the way, what should the application’s response be, in your opinion, if you do wrap both the DELETE and INSERT in a transaction, and the transaction gets rolled back? what do you do then?
the transaction is simply not needed
oh, and by the way, if a DELETE is run for rows which aren’t there, it will complete successfully
and if the transaction-less INSERT which then follows fails, just run it again too
Oh, but you are again talking about failed DELETEs so you are preaching to the choir. I’ll repeat for the third time: the transaction is to prevent data loss against failed INSERTs which come after successful DELETEs.
What do you mean by “the transaction gets rolled back”? Can a transaction get rolled back on its own? I’ve never heard of it - either you (the programmer) roll back the transaction on purpose or it continues to exist. There are some special cases like DDL statements causing implicit rollback in MySQL or certain errors like deadlocks but in this case there are none of those.
The application doesn’t need to check if a transaction gets rolled back because it can assume it is still running - at least as long as there are no errors.
Let’s take your own scenario:
A1 DELETE
A2 INSERT
If you don’t have a transaction then it is possible that A1 gets executed and before executing A2 something happens: the server can crash, hang, whatever - there can even be some innocent bug in the application code that results in a fatal error just before the insert. Some rows were deleted but none were inserted. From the user perspective: I had a form with 12 categories, I added one category, pressed Save, I got some error message but now when I come back to the form all my 12+1 categories are gone! Where is my data??
That’s what the transaction will protect against - those 12 categories will still be there.
some rows were deleted but not all? i don’t buy that scenario at all
also, what does the application do here when this happens? simply go back to the user and now the 12+1 categories are what… not gone?
look, i don’t want to drag this out… go ahead and use your transcation to wrap both statements
you’ll feel much better 
Some rows in the scope of the whole table - to be precise in this case 12 rows were deleted - those that referred to the main client table by the FK. So from the user’s perspective all 12 categories for that client were deleted.
This is beyond the scope of our considerations here because maybe the application may not even be able to do anything. What can a web application do when the whole server crashes? It stops responding and doesn’t do anything - in the case of a web application the user can see a blank page, or if the crash or error wasn’t that serious then maybe the application may present some error message. In any case the execution is stopped, the database connection gets terminated and any remaining transaction gets rolled back. And if the DELETE+INSERT was in a transaction then at least the user will find those 12 rows intact instead of deleted.
What does me feeling better have anything to do with it? I clearly demonstrated how the transaction saves against data loss in this case.
But it’s true that I feel better when my application is coded to prevent data loss whenever possible, especially that a transaction is a very simple thing to implement - then why not use it?