Questions tagged [if-not-exists]

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

Filter by
Sorted by
Tagged with
0
votes
2answers
19 views

Get slave status using sql query inside if-else condition

How can I stop the slave if it is already running using sql query statements only? So far I tried to do something like below but getting sql syntax error. I think it is due to the borders of result ...
1
vote
0answers
40 views

Finding the different rows from two resultsets based on one of the resultsets (MINUS, NOT EXISTS)

I'm testing queries on two resultsets that have the same value in certain columns on Oracle 11g. I want to exclude the rows that share these columns from my final result based on the first table. Say ...
3
votes
1answer
2k views

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 ...
0
votes
1answer
145 views

No data inserted with this WHERE NOT EXISTS expression?

I want to move data from MySQL to PostgreSQL. I am using mysql.connector and psycopg2 for querying the two databases. When I run WHERE NOT EXISTS for my PostgreSQL DB, it's doesn't insert the data ...
2
votes
2answers
72 views

select data from table which dont have row in another table

I have 2 tables. First "product" (id). Second - "product_has_option_value" (product_id, option_value_id). How can I get product ids which doesn't have some option_value_id (for example 10)? "...
0
votes
0answers
734 views

Performing SELECT or INSERT if does not exist in a single SQL statement without using WITH

I am wondering if there is a way to perform SELECT or INSERT if the record does not exist in a single SQL statement without using WITH clauses (i.e. CTEs)? It would be cleaner to write such SQL in a ...
2
votes
1answer
144 views

Duplicate rows with a few changes in a table with a composite primary key, but only if target does not exist

Consider the following table: CREATE TABLE [xxx].[MyTable]( [period] [date] NOT NULL, [genusId] [int] NOT NULL, [subjectId] [int] NOT NULL, [waitingStageId] [tinyint] NOT NULL, [...
-1
votes
1answer
39 views

Surcharges that were not invoiced

I have a table named INVOICES. I want to select all the invoice numbers where a certain charge code is missing. I kindly ask your help to help me out with this. All I need is something like : Select *...
5
votes
1answer
558 views

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 ...
2
votes
1answer
812 views

Optimization: insert only not existing rows with additional conditions

I have two databases: 'target' database looks like CREATE TABLE parent ( erow_id integer PRIMARY KEY, -- these two columns is a composite key (uid) uid_p1 integer, uid_p2 integer, ); ...
3
votes
0answers
10k views

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 ...
4
votes
2answers
3k views

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 ...
19
votes
4answers
25k views

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 ...
-1
votes
1answer
40 views

select columns if a column has a value [closed]

I have RegiNum and EmpNum columns in users table. If RegiNum column has a value it's considered as a student. If RegiNum is filled, I want to select Name and ID from that table. I used select if(...
1
vote
3answers
2k views

Combine WHERE NOT EXISTS with ON DUPLICATE KEY UPDATE

I have 2 tables params and params_exceptions where params is supposed to be updated frequently by an external script. INSERT INTO params (extension, type, subtype, paramvalue) VALUES ('12345', 'FRNR1'...
0
votes
1answer
102 views

select string that doesn't contain a substring/field

TableA.content is varchar2(4000), which was populated from a CLOB value that contains markup and text for a website, paragraphs actually. TableB.keyword contains a string that I want to find in TableA....
1
vote
1answer
47 views

How to select rows based on other tables - MySql

I have tables like this: CheckTable (id) (user_id) [0] [403] UserTable (id) (username) [403] [hello] I want to select only the rows from the UserTable that do not have the user id row in the ...
1
vote
1answer
58 views

Two If's And An Else [closed]

I am trying to run this t-sql query but I keep getting an error of Incorrect syntax near the keyword 'END' How should this be re-written in order to execute properly? What I want to achieve is if ...
2
votes
2answers
71 views

Cannot build correct SELECT in MySql

Please help me to create a correct MySql query, which I failed to do. I have 2 tables in our DB: CREATE TABLE IF NOT EXISTS `user_audit` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id`...
1
vote
1answer
1k views

MS Access: adding column if not exist (or try/catch it)

this is an easy question, but I can't find out the solution... I have to check if a column exists and add it if the test is negative. How can I do with only sql code? This is the syntax for SQL ...
3
votes
2answers
585 views

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, ...
0
votes
1answer
42 views

Adding a constant IF not in an other table

So I have this query. SELECT * FROM `Comment` WHERE `Target` = '".$_GET['pp']."' AND `Id` NOT IN (SELECT `Target` FROM `Delete` WHERE `Type` = 'comment') ORDER ...
1
vote
1answer
7k views

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 ...
1
vote
1answer
186 views

SQL Server USE ignores IF block

I want to shrink the transaction log file of several databases using a maintenance plan. Those databases are mirrored, therefore the shrink task cannot be executed on the MIRROR databases, but the ...
3
votes
1answer
6k views

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: ...
5
votes
1answer
7k views

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 ...
1
vote
1answer
1k views

MySQL : LEFT JOIN not fetching required data

I'm using left join in MySQL as mentioned below: SELECT Q.QUESTION_ID, school_id FROM Q_MASTER AS Q LEFT JOIN ( SELECT keyword, school_id FROM ...
2
votes
2answers
1k views

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 ...
4
votes
3answers
8k views

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 ...
5
votes
2answers
5k views

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,...
5
votes
1answer
45k views

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,...
63
votes
4answers
87k views

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 ...