Is there a command to test an SQL query without executing it? ( MySQL or ANSI SQL )
Is there anything like this: TEST DELETE FROM user WHERE somekey = 45;
That can return any errors, for example that somekey doesn't exist, or some constraint violation or anything, and reporting how many rows would be affected, but not executing the query? I know you can easily turn any query in a select query that has no write or delete effect in any row, but that can lead to errors and it's not very practical if you want to test and debug many queries.
I realise this is a bit of an old question but for completeness...
If the intention is to find the query processing time without returning any rows (I need this quite often, I want to know how long a piece of code I am using will take without having it return a couple of million rows I am not interested in seeing) then the BLACKHOLE engine can be very useful:
For instance say I have 2 tables, t1 & t2, with millions of rows, that I am joining together. I want to check how long this is likely to take, in a GUI (SQLYog or mysql workbench or somesuch) without returning millions of rows that will eat up memory and presumably take time for the GUI to process and display. I use the blackhole engine to 'dump' the rows to nowhere. EG:
CREATE TABLE tBH (a TINYINT) ENGINE = BLACKHOLE; SELECT NOW(); -- Show start time INSERT tBH SELECT 1 FROM t1 LEFT JOIN t2 ON t1.key1 = t2.key1; SELECT NOW(); -- Show end time
Note that as I am just looking for execution time I do not bother returning all the columns (IE with "*") but just a placeholder ("1" in this case).
The only thing I know of is to wrap it in a transaction that is always rolled back:
BEGIN TRANSACTION DELETE FROM user WHERE somekey = 45; ROLLBACK TRANSACTION
Make sure you execute the entire block and not just the delete statement. Also, DO NOT run this on any production environment or any system where you cannot afford to lose the data.
In MySQL use this
START TRANSACTION; QUERY;
It is important to use ";" because if you don't, it won't work. For example
START TRANSACTION; UPDATE tableX SET colX = valueA, colY = valueB WHERE id=1
Reference here http://dev.mysql.com/doc/refman/5.0/en/commit.html
ANSI SQL: No.
MySQL: Maybe. The EXPLAIN keyword originally worked only with SELECT, but it might have been extended to UPDATE and DELETE by now.
As of MySQL 5.6, the EXPLAIN keyword works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.
If your query has a syntax error, then it will still fail, however if successful you will only see the results of an EXPLAIN and the query will not make any changes.
This is much simpler than doing schema changes, using temp tables, or aborting transactions as all you need to do is insert "EXPLAIN " in front of your existing query.
More information: https://dev.mysql.com/doc/refman/5.6/en/explain.html
To my knowledge no such thing exists. Furthermore it would not be an error if no somekey with value 45 did not exist. It would just not delete anything.
You can use F11 with Teradata SQL Assistant to do this
Kind of. Say you have a table that you want to update: "Entry". You can
SELECT Col1 = OTHER.Col4, Col2 = EXTRA.Col2, FROM dbo.Entry E INNER JOIN MYOTHERTABLE OTHER ON OTHER.Id = E.Id INNER JOIN MYEXTRATABLE EXTRA ON EXTRA.Id = OTHER.Id
In this instance, Col1 and Col2 are columns of the Entry table. If you wrote
UPDATE E SET
instead of the initial SELECT, you'd have your update. It doesn't work for all scenarios but, if it's a simple update, you can get a quick preview this way.