how to use one mysql query, update data without duplicate?

Question

id | title | text
1  | aa    | 
2  | aa    |
3  | aa    |

I have some data from json data, i am not sure how many datas have duplicate in it.

$json = '[{"a":"1","b":"bb"},{"a":"2","b":"cc"},{"a":"3","b":"bb"}]';
$array = json_decode($json);
foreach($array as $key){
    UPDATE table SET text = '".$key->b."' WHERE id = '".$key->a."' and title='aa'");
}

For example, as this situation, $key->b has 2 data bb from the json data, I only want update the first one and check if bb has already in the database, then ignore update.

id | title | text
1  | aa    | bb
2  | aa    | cc
3  | aa    |      <-ignore updtae, left the data empty

I know there have an easy way, first select * from table where text != '$key->a' for check, but this will cost 2 mysql query and make one more foreach, so

how to use one mysql query, update data without duplicate?

many thanks.

Emmanuel Sys · Accepted Answer · 2011-11-12 12:30:28Z

1

If your database is MySQL, maybe you can use the ON DUPLICATE KEY UPDATE.

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

answered Nov 12, 2011 at 12:30

Emmanuel Sys

8136 silver badges12 bronze badges

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

1 Comment

fish man Over a year ago

@user1043095, this is insert? But I need update, the first 2 fields already in the table.

Kaivosukeltaja · Accepted Answer · 2011-11-12 12:40:35Z

0

I suggest using an array to store all the values of b you have used so far and only run the UPDATE if the value didn't exist yet.

$json = '[{"a":"1","b":"bb"},{"a":"2","b":"cc"},{"a":"3","b":"bb"}]';
$usedValues = array();
$array = json_decode($json);
foreach($array as $key){
    if(!isset(usedValues[$key->b])) {
        mysql_query("UPDATE table SET text = '".$key->b."' WHERE id = '".$key->a."' and title='aa'");
        usedValues[$key->b] = true;
    }
}

EDIT: If your database already has values for text this may still produce duplicates, but you could do a SELECT DISTINCT ´text´ and populate the $usedValues array with them.

edited Nov 12, 2011 at 12:40

answered Nov 12, 2011 at 12:34

Kaivosukeltaja

15.8k5 gold badges45 silver badges71 bronze badges

1 Comment

fish man Over a year ago

thanks for your answer, this would just scan the current json data, but if I have many json data will insert into database, how to avoid insert the duplicate data if I do mysql update from another json data?(1.txt,2.txt,3.txt... ) json_decode(file_get_contents('1.txt'));

Collectives™ on Stack Overflow

how to use one mysql query, update data without duplicate?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related