Member Avatar for nav33n

Hi all ! I have been assigned a task to implement an insert query into table a only if a record doesn't exist in table c. I have been scratching my head for past 2 days and even my best friend google didn't help me much, so I am asking the question here.
So, here it is.
I have created dummy tables to simplify the table structure.

CREATE TABLE `test2`.`a` (
`counter` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 20 ) NOT NULL ,
`age` INT NOT NULL ,
`postcode` VARCHAR( 20 ) NOT NULL ,
`city` VARCHAR( 20 ) NOT NULL
) ENGINE = InnoDB; 




 CREATE TABLE `test2`.`b` (
`counter` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 20 ) NOT NULL ,
`age` INT NOT NULL ,
`postcode` VARCHAR( 20 ) NOT NULL ,
`city` VARCHAR( 20 ) NOT NULL ,
`country` VARCHAR( 20 ) NOT NULL
) ENGINE = InnoDB;

INSERT INTO `test2`.`b` (`counter` ,`name` ,`age` ,`postcode` ,`city` ,`country`)
VALUES (NULL , 'testuser1', '25', '123456', 'Some city', 'Some country'), 
(NULL , 'testuser2', '26', '234567', 'Some city', 'Some country');



 CREATE TABLE `test2`.`c` (
`counter` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`id` INT NOT NULL ,
`location` VARCHAR( 20 ) NOT NULL
) ENGINE = InnoDB; 

INSERT INTO `test2`.`c` (`counter` ,`id` ,`location`)
VALUES (NULL , '1', 'location1'),
(NULL , '2', 'location2');


 CREATE TABLE `test2`.`d` (
`counter` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`c_id` INT NOT NULL ,
`Department` VARCHAR( 20 ) NOT NULL
) ENGINE = InnoDB; 

INSERT INTO `test2`.`d` (`counter` ,`c_id` ,`Department`)
VALUES (NULL , '1', 'department1'),
(NULL , '1', 'department2');

And here is the query.

INSERT INTO a (name, age, postcode, city ) 
SELECT name, age, postcode, city 
FROM b
WHERE NOT 
EXISTS ( 
SELECT *
FROM c
WHERE counter = ( 
SELECT  c_id
FROM d
WHERE counter =  '3'))

The above query works perfectly fine. Let me explain what it does. If the last query (ie., after WHERE NOT EXISTS ) doesn't return any row, then it insert all the record(s) to table a from table b. Simple.. No problem..
The problem is, IF I want to insert a record to table a from table b on certain condition, ie.,

INSERT INTO a (name, age, postcode, city ) 
SELECT name, age, postcode, city 
FROM b where counter = 1
WHERE NOT 
EXISTS ( 
SELECT *
FROM c
WHERE counter = ( 
SELECT  c_id
FROM d
WHERE counter =  '3'))

this doesn't work! The following query doesn't work too.

INSERT INTO a (name, age, postcode, city ) 
(SELECT name, age, postcode, city 
FROM b where counter = 1) 
WHERE NOT 
EXISTS ( 
SELECT *
FROM c
WHERE counter = ( 
SELECT  c_id
FROM d
WHERE counter =  '3'))

However, a simple query like

INSERT INTO a( name, age, postcode, city )
SELECT name, age, postcode, city
FROM b
WHERE counter =1

works fine. I am at my wit's end trying to find a solution!

I hope I have framed the question well enough. Any help will be appreciated ! :) Thanks in advance.

  • 1 Contributor
  • 1 Reply
  • 2K Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by nav33n

Dani AI

Generated

Nice catch, . The second WHERE was the blocker: MySQL only allows one WHERE per SELECT, so your final version that folds the NOT EXISTS into the same predicate with AND is the right fix. Also note that your NOT EXISTS condition is global (not correlated to each row of b), which is what you wanted: either insert the chosen b rows or none at all depending on c/d.

If you want a slightly cleaner shape (and avoid a scalar subquery), join inside EXISTS. Use parameters instead of hard-coded values and keep ints unquoted.

INSERT INTO a (name, age, postcode, city)
SELECT b.name, b.age, b.postcode, b.city
FROM b
WHERE b.counter = ?
AND NOT EXISTS (
  SELECT 1
  FROM d
  JOIN c ON c.counter = d.c_id
  WHERE d.counter = ?
);

A few practical tips to keep this fast and safe:

  • Indexes: you already have PKs on b.counter, c.counter, d.counter. Add an index on d(c_id) for the join above.

  • Duplicates in a: if reruns are possible, guard with a unique key on the natural candidate columns and decide how you want duplicates handled.

    ALTER TABLE a ADD UNIQUE KEY uq_a_name_postcode_city (name, postcode, city);

  • Plan check: run EXPLAIN on the SELECT to confirm it uses the PK on c.counter and the index on d(c_id).

  • Avoid NOT IN here; NULLs can make it behave unexpectedly, whereas NOT EXISTS is null-safe.

Member Avatar for nav33n

:icon_redface: After numerous attempts, I found out the solution. Here it is.

INSERT INTO a (name, age, postcode, city ) 
SELECT name, age, postcode, city 
FROM b where counter = 1
AND NOT 
EXISTS ( 
SELECT *
FROM c
WHERE counter = ( 
SELECT  c_id
FROM d
WHERE counter =  '3'))

Thanks for looking ;)

commented: Thanx for sharing +17
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.