Insert if not exists

How to have only 3 rows in the table and only update them?

I have the settings table and at first run there is nothing so I want to insert 3 records like so:

id | label | Value | desc
--------------------------
1    start    10       0
2    middle   24       0
3    end      76       0

After this from PHP script I need to update this settings from one query. I have researched REPLACE INTO but I end up with duplicate rows in DB.

Here is my current query:

$query_insert=" REPLACE INTO setari (`eticheta`, `valoare`, `disabled`) 
                VALUES ('mentenanta', '".$mentenanta."', '0'), 
                       ('nr_incercari_login', '".$nr_incercari_login."', '0'), 
                       ('timp_restrictie_login', '".$timp_restrictie_login."', '0') 
              "; 

Any ideas?

Here is the create table statement. Just so you can see in case I'm missing something.

CREATE TABLE `setari` (
    `id` int(10) unsigned NOT NULL auto_increment,
    `eticheta` varchar(200) NOT NULL,
    `valoare` varchar(250) NOT NULL,
    `disabled` tinyint(1) unsigned NOT NULL default '0',
    `data` datetime default NULL,
    `cod` varchar(50) default NULL,
    PRIMARY KEY  (`eticheta`,`id`,`valoare`),
    UNIQUE KEY `id` (`eticheta`,`id`,`valoare`)
) ENGINE=MyISAM

As explained in the manual, need to create a UNIQUE index on (label,value) or (label,value,desc) for REPLACE INTO determine uniqueness.

What you want is to use 'ON DUPLICATE KEY UPDATE' syntax. Read through it for the full details but, essentially you need to have a unique or primary key for one of your fields, then start a normal insert query and add that code (along with what you want to actually update) to the end. The db engine will then try to add the information and when it comes across a duplicate key already inserted, it already knows to just update all the fields you tell it to with the new information.

I simply skip the headache and use a temporary table. Quick and clean.

SQL Server allows you to select into a non-existing temp table by creating it for you. However mysql requires you to first create the temp db and then insert into it.

1. Create empty temp table.

CREATE TEMPORARY TABLE IF NOT EXISTS insertsetari
SELECT eticheta, valoare, disabled
FROM setari
WHERE 1=0

2. Insert data into temp table.

INSERT INTO insertsetari
VALUES
('mentenanta', '".$mentenanta."', '0'),
('nr_incercari_login', '".$nr_incercari_login."', '0'),
('timp_restrictie_login', '".$timp_restrictie_login."', '0')

3. Remove rows in temp table that are already found in target table.

DELETE a FROM insertsetari AS a INNER JOIN setari  AS b
WHERE a.eticheta = b.eticheta
  AND a.valoare = b.valoare
  AND a.disabled = b.disabled

4. Insert temp table residual rows into target table.

INSERT INTO setari
SELECT * FROM insertsetari

5. Cleanup temp table.

DELETE insertsetari

Comments:

• You should avoid replacing when the new data and the old data is the same. Replacing should only be for situations where there is high probability for detecting key values that are the same but the non-key values are different.
• Placing data into a temp table allows data to be massaged, transformed and modified easily before inserting into target table.
• Deleting rows from temp table is faster.
• If anything goes wrong, temp table gives you an additional debugging stage to find out what went wrong.
• Should consider doing it all in a single transaction.