SQL insert into table if row not exists in empty table not working

Question

I've a problem, I want insert fields in a table if row not exist

My code is:

INSERT INTO  `myTable` (
`circuit` ,
`date` ,
`session` ,
`lap` ,
`time`
) SELECT  'misano',  '2013-10-11',  1,  1,  '0:01:06:332'
FROM `myTable`
WHERE NOT EXISTS (SELECT 1 FROM `myTable` WHERE `circuit` = 'misano' AND `date` = '2013-10-11' AND `session` = 1 AND `lap` = 1 AND `time` = '0:01:06:332')

This code work fine if in "myTable" there is at least one row. If "myTable" is empty, SQL return: 0 row.

Fabian · Accepted Answer · 2013-10-20 15:06:35Z

5

If myTable is empty, then a SELECT ... FROM myTable will never produce any rows (even without the WHERE condition).

Use

FROM dual

in the outer select. The DUAL pseudo table always contains exactly one row. The EXISTS sub-query runs against myTable of course.

Edit:

If you have a unique index on myTable, see the solution suggested by Filipe.

edited Oct 20, 2013 at 15:06

answered Oct 20, 2013 at 14:38

Fabian

2,9901 gold badge20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Filipe Silva · Accepted Answer · 2013-10-20 14:40:00Z

4

If you have a primary key or unique index in your table, you could use INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO `myTable` (`circuit`, `date`, `session`, `lap`, `time`)
    VALUES ('misano', '2013-10-11', 1, 1, '0:01:06:332') 
ON DUPLICATE KEY
UPDATE `circuit` = VALUES (`circuit`), 
        `date` = VALUES (`date`), 
        `session` = VALUES (`session`), 
        `lap` = VALUES (`lap`), 
        `time` = VALUES (`time`)

answered Oct 20, 2013 at 14:40

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

3 Comments

Flavio Bontà Over a year ago

if a row exist this return a new same row with id=id+1

Filipe Silva Over a year ago

If the column with unique index already exists, this should update the old values for that record. But if you are excluding an auto_incremented id, then yes, that will always insert and never update, since you are saying you want a different id for this row, thus never triggering the update.

Flavio Bontà Over a year ago

however I do not have an index, thank's for your patience. Fabian Büttner solution is perfect for me :)

Collectives™ on Stack Overflow

SQL insert into table if row not exists in empty table not working

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related