21
votes
Find IDs from a list that don't exist in a table
One way of doing it would be to use VALUES to create a table expression with the ids to check and EXCEPT to find the missing ones.
SELECT id
FROM (VALUES(4),(5),(6)) V(id)
EXCEPT
SELECT id
FROM ...
17
votes
Find IDs from a list that don't exist in a table
While using EXCEPT like @Martin provided, remember to make it EXCEPTALL, unless you want to pay a little extra for trying to fold duplicates.
BTW, a VALUES expression can stand on its own:
VALUES (4),(...
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';
...
7
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 ...
6
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 ...
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 ;
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
...
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
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 ...
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 ...
3
votes
Accepted
Combine WHERE NOT EXISTS with ON DUPLICATE KEY UPDATE
The problem in your query is this part:
ON DUPLICATE KEY UPDATE
--
paramvalue = t.extension ;
The alias t is not visible in that part of the query.
However, assuming that the two values, ...
2
votes
Find IDs from a list that don't exist in a table
Just use a second table and join them.
create table images1(
id int not null
);
create table images2(
id int not null
);
insert into images1 values(1), (2), (3), (4), (6), (8);
insert into ...
2
votes
Accepted
select columns if a column has a value
There are a couple of options here. One is to repeat the given structure for each column:
select
if(EmpNum is null,"",ID) as s_id,
if(EmpNum is null,"",Name) as s_name,
.. etc
from users
where ...
2
votes
How to select rows based on other tables - MySql
Is this what you're looking for?
SELECT Whatever FROM UserTable
WHERE UserTable.username NOT IN
(
SELECT CheckTable.user_id FROM CheckTable
);
If this is not suitable, please expand. Possibly ...
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 ...
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'...
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.
1
vote
Accepted
INSERT-ing to a table if the the data does not already exits - how do indexes peform 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,...
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 ...
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 = '...
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 ...
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 ...
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&...
Only top scored, non community-wiki answers of a minimum length are eligible
Related Tags
if-not-exists × 35mysql × 15
sql-server × 7
join × 5
insert × 5
sql-server-2008 × 4
postgresql × 3
stored-procedures × 3
select × 3
index × 2
exists × 2
oracle × 1
performance × 1
sql-server-2012 × 1
t-sql × 1
mariadb × 1
query × 1
mysql-5.6 × 1
update × 1
oracle-12c × 1
foreign-key × 1
azure-sql-database × 1
index-tuning × 1
oracle-11g × 1
ms-access × 1