Questions tagged [if-not-exists]
if-not-exists is a common SQL condition used to check if an entity exists.
35
questions
2
votes
1
answer
2k
views
Problem with IF NOT EXISTS query in ms sql
I want to script query to validate if not have existing column test to add it after check condition. But in those two ways it is not working.
Way 1:
IF [dbo].[TEST].TEST NOT EXISTS
BEGIN
ALTER TABLE [...
- 23
0
votes
1
answer
1k
views
How would I use "LEFT JOIN" and "IF NOT EXISTS" in this case?
There're 2 main tables: drivers and driver_riders that are connected by a foreign key. Every day some of the drivers make a single or multiple rides.
There's also a table driver_rewards
driver_rewards
...
- 35
0
votes
1
answer
68
views
INSERT-ing to a table if the the data does not already exits - how do indexes peform in this scenario?
When you are in the position of inserting data into a table if the data does not already exist ...what is the best way to go with indexes?
I'm specifically thinking of SQL Server here but I assume the ...
- 123
3
votes
1
answer
53
views
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 ...
- 33
0
votes
2
answers
94
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 ...
- 125
1
vote
0
answers
53
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 ...
- 111
4
votes
1
answer
8k
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 ...
- 143
0
votes
1
answer
246
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 ...
- 215
2
votes
2
answers
1k
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)? "...
- 23
2
votes
1
answer
647
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,
[...
- 691
-1
votes
1
answer
40
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 *...
- 3
5
votes
1
answer
1k
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 ...
- 341
2
votes
1
answer
2k
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,
);
...
- 23
3
votes
0
answers
15k
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 ...
- 209
4
votes
2
answers
5k
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 ...
- 849
60
votes
4
answers
115k
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
1
answer
44
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
3
answers
3k
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'...
- 13
1
vote
2
answers
2k
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....
- 11
1
vote
1
answer
83
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 ...
- 11
1
vote
1
answer
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
2
answers
77
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`...
- 21
1
vote
2
answers
3k
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 ...
- 143
3
votes
2
answers
591
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, ...
- 33
0
votes
1
answer
44
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 ...
2
votes
1
answer
8k
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 ...
- 235
1
vote
1
answer
197
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 ...
- 57
3
votes
1
answer
8k
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:
...
- 471
6
votes
1
answer
10k
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 ...
- 97
1
vote
1
answer
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 ...
- 125
2
votes
2
answers
2k
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 ...
- 155
4
votes
3
answers
9k
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 ...
- 143
5
votes
2
answers
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,...
- 151
7
votes
1
answer
52k
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, ...
- 195
93
votes
4
answers
171k
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 ...
- 11.5k