11
votes
MySQL: Create index If not exists
If you name the index, the query will fail if the index already exists (tested in MySQL 8.0):
ALTER TABLE `my_table` ADD INDEX `col_idx` (`col` DESC);
Error Code: 1061. Duplicate key name 'col_idx';
...
- 362
8
votes
MySQL: Create index If not exists
SELECT COUNT(*)
FROM information_schema.statistics
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'table_name'
AND INDEX_NAME = 'index_name';
My query would give you the count of indexes ...
- 191
7
votes
Accepted
select data from table which dont have row in another table
What you want is called "anti-join" (or "anti-semijoin"). There are 3 main ways to do this kind of query in MySQL (and some more in other DBMS, that have implemented EXCEPT operator):
NOT IN subquery ...
- 98.7k
5
votes
Accepted
Check table before delete a constraint
You can use information_schema to check if the table exists but in MySQL there is a simpler method:
DROP TABLE IF EXISTS employees ;
- 98.7k
5
votes
Accepted
Problem with IF NOT EXISTS query in ms sql
If you want to script conditional addition of a column (only if it does not already exist in the table), you can use the sys.columns system catalogue in this way:
IF NOT EXISTS
(
SELECT
*
FROM
...
- 23.2k
3
votes
Accepted
Help filtering out records in MYSQL
SELECT koalaName
FROM main
GROUP BY koalaName
HAVING SUM( fate IN ('Released', 'Relocated', 'Care') ) > 0
AND SUM( fate = 'Found Dead') = 0
- 20.3k
3
votes
Accepted
Optimization: insert only not existing rows with additional conditions
Besides adding indexes (which you say is not allowed), the query is unnecessarily complex. The 2 of the 3 table references in the correlated subquery are not needed as they join to the same tables in ...
- 98.7k
3
votes
Accepted
How do I identify all the students who have passed?
The problem you describe is known as relational division. There are basically two ways to approach it:
Transform for all x : p(x) to not exists x : not p(x)
In other words, transform students who ...
- 23.6k
2
votes
Accepted
Duplicate rows with a few changes in a table with a composite primary key, but only if target does not exist
Taking what your query says literally, you don't have to worry about what exists in xxx.myTable - because your statement will create it, therefore it won't have anything in it (or, if it already ...
- 11.7k
2
votes
Accepted
No data inserted with this WHERE NOT EXISTS expression?
Your condition compares the column value to itself:
WHERE NOT EXISTS(SELECT code FROM subject WHERE code = code)
Boils down to:
WHERE NOT EXISTS(SELECT FROM subject WHERE code IS NOT NULL)
IOW, it'...
- 182k
2
votes
MySQL: NOT EXISTS vs LEFT OUTER JOIN...IS NULL
I made a test environment to get an idea which one will be faster.
MySQL version
8.0.39-0ubuntu0.22.04.1
Tables structure
CREATE TABLE `table1` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`a` ...
- 4,463
2
votes
Get slave status using sql query inside if-else condition
Why not simply do STOP SLAVE? If not "slave_running", it will have no effect.
- 79.4k
2
votes
How can I select all rows from two SQLite tables, but prefer one if there are matching rows in both?
Here is the FULL OUTER JOIN solution with COALESCE() that mustaccio alluded to:
SELECT
COALESCE(actual.round, planned.round) AS round,
COALESCE(actual.id, planned.id) AS id,
COALESCE(...
- 39.5k
1
vote
How can I select all rows from two SQLite tables, but prefer one if there are matching rows in both?
Per what nbk said, if you UNION ALL the two tables together, you can then use a window function like ROW_NUMBER() to enumerate all of the rows and prioritize the ones that exist in the actual table, ...
- 39.5k
1
vote
Accepted
How would I use "LEFT JOIN" and "IF NOT EXISTS" in this case?
Well you could use an outer join like LEFT JOIN here or NOT EXISTS but you don't need both. I prefer outer joins because they feel more relational to me. Here's how you can accomplish your goals with ...
- 39.5k
1
vote
Accepted
INSERT-ing to a table if the data does not already exist - how do indexes perform in this scenario?
A clustered index IS the table - so if the rows you are adding are already ordered there is not much work to do. A non-clustered index is an extra structure that you will have to maintain - so Inserts,...
- 4,160
1
vote
Accepted
Check if table exists on a linked server
You can query the Information_Schema views on the linked server:
if exists(
select *
from [Linked_Server_Name].[Database_Name].INFORMATION_SCHEMA.TABLES
where table_name ...
- 166
1
vote
Accepted
Surcharges that were not invoiced
Will this work for you? Assuming Invoices has an ID column which you can replace in this code:
SELECT * FROM Invoices WHERE InvoiceID NOT IN
(SELECT InvoiceID FROM Invoices WHERE chargecode = '...
- 709
1
vote
How do I identify all the students who have passed?
How about this:
USE tempdb
CREATE TABLE dbo.Student (
s_idn int,
name varchar(30)
);
CREATE TABLE dbo.Exam (
e_idn int IDENTITY,
s_idn int,
pass char(1),
active char(1)
)
GO
INSERT INTO ...
- 3,083
1
vote
select string that doesn't contain a substring/field
I believe the problem could be summarised like this:
Select a row from TableA if no row exists in TableB such that TableB.keyword is a substring of TableA.content.
It would probably be fair to say ...
- 23.2k
1
vote
MS Access: adding column if not exist (or try/catch it)
tableexist = CheckTableExistance("Tbl_Name");
if (tableexist == true)
{
using (System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("select * from Tbl_Name&...
- 21
Only top scored, non community-wiki answers of a minimum length are eligible