SQL - Verification of existing data

Question

I have a script always verifies that the row exists or not, depending on adjusts to the event.

For example:

if(exists($row)){
    // update
}else{
    // insert
}

Insert query:

INSERT INTO table (name, value) VALUES 
    ('...', '1'), 
    ('...', '2'), 
    ('...', '3'), 
    ('...', '4'), 
    ('...', '5'), 
    //...100x

UPDATE QUERY:

 UPDATE table
     SET value = CASE uuid
         WHEN 'x' THEN 1
         WHEN 'y' THEN 2
     END,
     SET value1 = CASE uuid
         WHEN 'x' THEN 1
         WHEN 'y' THEN 2
     END
     WHERE uuid IN ('x','y')

Data is quite a lot, and verify that the record exists long enough. Because performs necessary 500x or more.

Exists method:

SELECT id FROM table WHERE uuid=Y;

There is a possibility somehow speed up the script? It is possible to verify existing data added directly to the query?

Can I have just one query CREATE + UPDATE + CHECK EXISTING?

Thanks for help!

Updating and adding data with verification : 27-40 SEC
Updating and adding data WITHOUT verification: 1-5 SEC

EDIT: I'm getting data from another server like json example script:

$data = json_decode($_POST["data"]);
foreach($data as value){
    // value is an array
    if(exists($value["uuid"])){
         $this->appendUpdate($value); // building the update query
    }else{
         $this->appendInsert($value); // building the insert query
    }
}

    $this->insert();  // for example: 30 inserts in 1 query
    $this->update();  // for example: 500 updates in 1 query

In this table is just 300-2000 rows (I do not understand why it takes so long.)

EDIT2:

This is probably my solution:

INSERT INTO test (uuid, value) VALUES
    ('aaa', 1),
    ('bbb', 2),
    ('ccc', 3)
    ON DUPLICATE KEY UPDATE
    value = (
        IF(uuid='aaa', 4, IF(uuid='bbb', 5, IF(uuid='ccc', 6, value)))
    );

I'm going to try to run the brisk server and then touch.

make table view using select and then insert if you can. Are you taking insert data from the database itself or you have local data totally? — MindGamer
– MindGamer, Commented Sep 2, 2016 at 23:22
I would use 1) transactions. 2) a prepared query outside the loop. I suspect it will be quite fast. Especially with the insert .. on duplicate ... query. It is easy easy to code as well. — Ryan Vincent
– Ryan Vincent, Commented Sep 3, 2016 at 0:04

MarcHoH · Accepted Answer · 2016-09-02 23:24:57Z

1

Make sure the column uuid has a unique index and you can do the following query

INSERT INTO table (a,b) VALUES (1,2)
   ON DUPLICATE KEY UPDATE b=4;

answered Sep 2, 2016 at 23:24

MarcHoH

3401 silver badge6 bronze badges

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

6 Comments

user4256388 Over a year ago

I can't use UUID as primary key, cause the primary key is 'ID' - UUID is just primary key in another server - not mine .

MarcHoH Over a year ago

A unique index is something else than a primary key. they are pretty similar but you can have multiple unique indexes per table, so that shouldn't be a problem.

user4256388 Over a year ago

aaaaa hmmm it's probably working now (on differant test table) I'll try to make it work in the real script. @MarcHoH

user4256388 Over a year ago

But, I need just one query, not 1000x inserts like this it will be the same slow effect like verification probably. Is there way to make it on one query? Like this? gist.github.com/anonymous/bdbe2da59434adc798d0ac28a513b4ea @MarcHoH

MarcHoH Over a year ago

It is possible to use multiple inserts, like so: insert into table (a,b) values (1,2), (2,4) on duplicate key update b=5 . but just like the normal update statements you only can have one on duplicate key update.

|

Pang · Accepted Answer · 2016-09-03 05:39:12Z

0

Something like this:

INSERT INTO table(name,value)
SELECT table2name as name, table2value as value FROM table2 
WHERE uuid IS NULL;

edited Sep 3, 2016 at 5:39

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Sep 2, 2016 at 23:40

MindGamer

1361 silver badge10 bronze badges

Collectives™ on Stack Overflow

SQL - Verification of existing data

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related