How do I check if data exists in my SQL table and if it doesn't insert the newer data?

Question

My table is made up of 3 values-

long
short
id

id is an auto increment int value and the rest are text.

My PHP script retrieves the latest version of a program every half an hour and stores it as long in an sql table. What I want is for the php script to check if the value is already in the table(i.e. there are no new versions). If it isn't I want it to insert the new data. Just to make this clear I am not replacing the value "long". I am merely making a new value of it and 'id' is keeping a record of how many versions there have been.

IamIC · Accepted Answer · 2010-11-28 08:55:37Z

4

Use the EXISTS Keyword.

INSERT INTO Table (Col1, Col2, Col3)
Values(?, ?, ?)
WHERE NOT EXISTS (
SELECT Col1 FROM TABLE
WHERE Col1 = ? AND Col2 = ? AND Col3 =?)

You can work out which columns you actually need to test.

answered Nov 28, 2010 at 8:55

IamIC

18.4k20 gold badges97 silver badges162 bronze badges

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

Comments

Fabian Iwand · Accepted Answer · 2010-11-28 12:38:50Z

0

You can put a UNIQUE index on the long column and use INSERT IGNORE:

INSERT IGNORE INTO versions (long, short)
VALUES (?, ?)

If the key already exists MySQL will generate a warning (not an error) and will neither insert nor update any rows.

answered Nov 28, 2010 at 12:38

Fabian Iwand

2732 silver badges11 bronze badges

Collectives™ on Stack Overflow

How do I check if data exists in my SQL table and if it doesn't insert the newer data?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related