Use ON DUPLICATE KEY UPDATE On query

Question

i want use ON DUPLICATE KEY UPDATE on my insert query , but display error "SQLSTATE[HY000]: General error: 2031 SQLSTATE[HY000]". my query code is :

    foreach ($update_config_array as $a => $b){
                $temporary_data =array(
                    "a" => $a,
                    "b" => $b
                );
                $insert_update_query = "INSERT INTO `tbmedia_config` (`config_name`,`config_value`) VALUES (:a,:b) ON DUPLICATE KEY UPDATE `config_name` = :a , `config_value` = :b";
$conn->Tbmedia_runquery($insert_update_query,$temporary_data);

and my render query method is :

// Execute query
    public function Tbmedia_runquery($query_body = "" ,  $parameter_array = "" ){
        $query = $query_body;

        try{
            $stmt = $this->Tbmedia_connection->prepare($query);

            if(is_array($parameter_array)){
                // declare bind_param variable
                foreach ($parameter_array as $key => &$value) {
                    $stmt->bindParam(":$key",$value);
                    //':$key'
                }
            }

            $stmt->execute();
            return $stmt;
        }catch (\Exception $e){
            echo $e->getMessage();
        }
    }

where is my wrong ?

Solved The problem is here , cant use same variable for insert and update In other words cant use :a and :b same for insert and ON DUPLICATE KEY UPDATE Instead of use this code :

foreach ($update_config_array as $a => $b){
                $temporary_data =array(
                    "a" => $a,
                    "b" => $b,
                    "c" =>$a,
                    "d" =>$b
                );
                $insert_update_query = 'INSERT INTO `tbmedia_config` (`config_name`,`config_value`) VALUES (:a,:b) ON DUPLICATE KEY UPDATE `config_name` = :c , `config_value` = :d';


                $conn->Tbmedia_runquery($insert_update_query,$temporary_data);
            }

1) You're trying to update the `config_value` twice in your UPDATE clause. 2) If you're just going to update them to the same values, then just use `INSERT IGNORE` to skip the update on duplicate keys. — aynber, Oct 03 '19 at 15:49

yergo · Answer 1 · 2019-10-03T16:35:00.447

0

You cant use same parameter name in one query. It means, f you used ":a" within "VALUES(:a, :b)", yoou cant use it second time within "ON DUPLICATE KEY UPDATE config_name = :a".

Proper query would be:

INSERT INTO 
  tbmedia_config (config_name, config_value) 
  VALUES 
    (:a,:b) 
  ON DUPLICATE KEY UPDATE 
  config_name = VALUES(config_name), config_value = VALUES(config_value)

If you use in SQL config_name = VALUES(config_name), it will transfer in place of VALUES(config_name), the value you have passed as :a.

edited Oct 03 '19 at 16:35

answered Oct 03 '19 at 15:44

yergo

4,761
2
19
41

By mistake use twice config_value on query in hrere , edit it , please see again – Faramarz Tayyari Oct 03 '19 at 16:17
Please, take a point on me using `config_value = VALUES(config_value)`. This is SQL semantics. – yergo Oct 03 '19 at 16:29

user3265030 · Answer 2 · 2019-10-03T16:35:54.590

-1

maybe you just forgot second parameter?

use

$conn->Tbmedia_runquery($insert_update_query, $temporary_data);

instead

$conn->Tbmedia_runquery($insert_update_query);

PS: The question has been edited after this answer. Problem was in something else.

edited Oct 03 '19 at 16:35

answered Oct 03 '19 at 15:53

user3265030

194
1
3

No , I had forgotten write the second parameter here , No problem with that – Faramarz Tayyari Oct 03 '19 at 16:15

Use ON DUPLICATE KEY UPDATE On query

2 Answers2