Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [if-not-exists]

if-not-exists is a common SQL condition used to check if an entity exists.

IF NOT EXIST conditions are used in WHERE clauses, in IF statements and in conditional creation of entities, e.g.:

CREATE TABLE IF NOT EXISTS cars(
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
maker VARCHAR(30),
model VARCHAR(30));
36 questions
96
votes
4 answers

MySQL: Create index If not exists

Is there a way to create an index in MySQL if it does not exist? MySQL does not support the obvious format: CREATE INDEX IF NOT EXISTS index_name ON table(column) ERROR 1064 (42000): You have an error in your SQL syntax;... MySQL version (mysql -V)…
Adam Matan
  • 12,079
  • 30
  • 82
  • 96
63
votes
4 answers

Find IDs from a list that don't exist in a table

Say I have the following schema and data: create table images( id int not null ); insert into images values(1), (2), (3), (4), (6), (8); I want to perform a query like: select id from images where id not exists in(4, 5, 6); But this doesn't…
Patrick D'appollonio
  • 735
  • 1
  • 5
  • 7
7
votes
1 answer

MySQL: Execute CREATE TEMPORARY TABLE SELECT just one time?

I want to create a temporary table with some select-data. My (strange) problem is that I have to do the same query more than one time. CREATE TEMPORARY TABLE IF NOT EXISTS cache (id int(11) NOT NULL, INDEX (id)) SELECT id FROM table WHERE…
Timo
  • 195
  • 1
  • 4
  • 11
6
votes
1 answer

Increment value in row if it exists, otherwise create row with value?

Below is the MySQL table layout that I'm working with. Basically, I'd like to increment views, watchers, and inquiries as they occur through the front end of the website. My question is how would I go about having a new _views row for instance…
Jerry Tunin
  • 97
  • 1
  • 1
  • 6
5
votes
2 answers

What can I do to speed up this SQL Query?

I've created this SQL query with the help of @Dems :-) Here is some detail, I tried to make a SQLFiddle but I kept getting errors with my variables... This works in Sql Server 2008... My question is, how can I make my query faster? I know I'm doing…
Faraday
  • 151
  • 1
  • 3
5
votes
1 answer

Check table before delete a constraint

In SQL Server I used the following code to drop a table with a constraint: IF OBJECT_ID('EMPLOYEES') IS NOT NULL BEGIN ALTER TABLE EMPLOYEES DROP CONSTRAINT EMP_DEPT_FK DROP TABLE EMPLOYEES; END How can I accomplish the same…
andrea
  • 341
  • 1
  • 6
  • 14
4
votes
2 answers

MySQL: NOT EXISTS vs LEFT OUTER JOIN...IS NULL

Is NOT EXISTS or LEFT OUTER JOIN...IS NULL more efficient for finding records in one table that aren't in another? Specifically, I'm trying to figure out which of the two queries below is better: SELECT table1.id FROM table1 WHERE table1.a='FOO' AND…
Thomas Johnson
  • 481
  • 2
  • 5
  • 13
4
votes
3 answers

Create procedure in else block on SQL Server

I have a SQL script in which I generate a database and its tables, stored procedures, views, etc. I have used following script to generate the database and all the things. I was able to generate tables but on stored procedure it's giving an…
sharad
  • 143
  • 1
  • 7
4
votes
1 answer

Check if table exists on a linked server

I've had a search, but can't find a post relating to this instance specifically. We have a linked production database that I'm trying to reference from our 'sandbox' server. I want to check if a table exists on the LINKED production server. For any…
Jon
  • 143
  • 1
  • 3
4
votes
2 answers

How do I identify all the students who have passed?

Suppose I have a Table student and a Table exam which tracks all the exams a student has taken, how do I find all the students who have passed or not taken any exams similar to a white-list? In this example that means students 1 and 2. PS: pass flag…
Nishant
  • 899
  • 2
  • 13
  • 20
3
votes
2 answers

Can anybody tell me what's wrong with this query?

CREATE PROCEDURE Click( in ipaddress varchar(45)) BEGIN DECLARE ex int; SELECT COUNT(*) INTO ex FROM clickactivity WHERE IP = ipaddress; IF ex = 0 THEN INSERT INTO clickactivity (IP, Clicks) VALUES(ipaddress, 0); END IF; …
Daniel Sharp
  • 33
  • 2
3
votes
1 answer

Help filtering out records in MYSQL

I hope that someone can help me. I need to search to filter out values in a database. The database has about 14 000 records of Koalas that have come into care. Each Koala has their own name with a fate that has happened with it. I need to extract…
Rod-Miller
  • 33
  • 3
3
votes
0 answers

DROP DATABASE IF EXISTS CREATE DATABASE IF NOT EXISTS USE in PostgreSQL

I always was used following header in files that defined database in MySql: DROP DATABASE IF EXISTS base; CREATE DATABASE IF NOT EXISTS base; USE base; In PostgreSQL there are the following problems. IF NOT EXISTS is not definied, USE is not…
Daniel
  • 209
  • 1
  • 3
  • 10
2
votes
1 answer

Stored Procedure to evaluate column if condition is true execute another stored procedure

I have a stored procedure that executes with Windows task manager and I want to add a condition statement that executes another stored procedure if the condition is true. I was thinking a case statement but I get a invalid syntax near CASE and…
zerodoc
  • 235
  • 3
  • 9
2
votes
2 answers

Check if row exists

I need to write a query that will insert a row only once, even if the query is run multiple times. Being new to SQL (well new-ish), I did an if not exists(...) but a friend said he preferred deleting the row if it exists and then adding it…
robasta
  • 155
  • 1
  • 5
1
2 3