I would like to delete a record from one table. It is linked to 3 other tables and would like to delete all occurances with the same ID. I would rather not do it with 4 separate queries, that seems nuts. Can it done and if so what is the process called.
MySQL by the way.
Cheers
This problem is commonly solved by using transaction. You start a new transaction, do several inserts, deletes, etc. and if all goes well, you finally submit the transaction. If there were errors you roll back the transaction and the state of the database returns to the one before you started the transaction.
MySQL uses an atomic approach (but supports transactions on certain table types now). Each update, delete, insert is done on its own. This involves somewhat more work on the side of the programmer but is often faster than the transaction approach.
See Transactions and Atomic Operations [mysql.com] for an in-depth discussion of those two concepts.
Andreas
BEGIN TRANSACTION
SQL 1
SQL 2
SQL n
COMMIT TRANSACTION
Or would you recommend SAVEPOINTS and ROLLBACKS as well.
Cheers
If you were a bank and wanted to transfer money from one account to the other you would start a new transaction, lower the balance on one account and increase it on the second one and then commit the transaction. If however, the query to increase the balance fails, you would issue a rollback which would put the money back into the first account.
Andreas
They're asking about 'cascading updates and deletes' in MySQL.
I did a quick search but didn't find a good link, but knowing the name will help you find it. In SQL7 you do it with triggers, I've not done it yet in MySQL.
I am having trouble finding resources on both really.
Cascade deletes/updates would be nice but at the moment I have a series of SQL statements to execute within the same call, but as Andreas mentioned I dont want it to do only half of the execution. All or nothing.
I know there is different syntax for multiple statements depending on the RDMB. Located SQL server and Oracles versions but neither work for me in mySQL.
Cheers
[mysql.com...]
Towards the end Rich Brome makes a comment about Commit occuring in the case of an error. That seems strange to me and would make the bank example difficult to work.
Is that right?
While stored procedures and triggers can save you a lot of work, they usually are proprietary extensions and prevent portability.
I read Rich Bromeīs comment as well. However, I donīt think it is that much of a problem. The commands that automatically end a transaction are not ones you would normally use in your application that often. They are mainly used for database maintenance. And even if you are worried about that, just issue a rollback command before any of the commands ending a transaction. An open transaction will be reversed. If every transaction was committed nothing will happen.
Andreas
eg,
create table People (
fullname varchar(64) primary key,
other fields...
)
create table Addresses (
name varchar(64) not null references People(fullname) on delete cascade,
other fields...
)
Now, if you delete a person, their address will get nuked, too.
It's a contrived example, may well have a syntax error or two, and if it works at all it works in PostgreSQL, since that's what I've been using much lately, but the concept is, I think, what you're after. The key part is the 'on delete cascade'.
If MySQL doesn't support cascading deletes, then I've wasted your time.
In MySQL Server, you can work around the problem of ON DELETE ... not being implemented by adding the appropriate DELETE statement to an application when you delete records from a table that has a foreign key. In practice this is as quick (in some cases quicker) and much more portable than using foreign keys.In MySQL Server 4.0 you can use multi-table delete to delete rows from many tables with one command. See section 6.4.6 DELETE Syntax [mysql.com].
[mysql.com ]
You can use Foreign Key Constraints [mysql.com] as explained by dingman with InnoDB type tables.
Andreas
