CODEWITHSUNDEEP
phpsqloracle-database
FreshPro
FreshPro

Reputation: 873

My query generates an ORA-00933 error, why is that?

I'm using php to generate an oracle query like this:

...

$sql = sprintf("INSERT INTO $table_name %s %s ON DUPLICATE KEY UPDATE ", 
            $this->prepare_insert_sql("", $fields, false), 
            $this->prepare_insert_sql(" VALUES ", $values, true));
    for ($index = 0; $index < count($fields); $index++) {
        if ($index > 0) {
            $sql .= ", ";
        }
        $sql .= $fields[$index] . "='" . $values[$index] . "'";
    }
...

And the result query is:

INSERT INTO TBL_CONFIG(KEY,VALUE) 
VALUES ('1_default_meter_type_for_device_type_1','822') 
ON DUPLICATE KEY 
UPDATE KEY='1_default_meter_type_for_device_type_1', VALUE='822'

It gives an ORA-00933 error.

I really can't seem to find the error. Any tip is appreciated.

Upvotes: 1

Views: 150

Answers (3)

FreshPro
FreshPro

Reputation: 873

Using merge instead of insert into worked.

MERGE INTO TBL_CONFIG USING DUAL ON (KEY ='1_default_meter_type_for_device_type_1')
WHEN MATCHED THEN UPDATE SET VALUE = '822'
WHEN NOT MATCHED THEN INSERT (KEY, VALUE) VALUES ('1_default_meter_type_for_device_type_1', '822')

Upvotes: 2

Rahul
Rahul

Reputation: 77896

Per your posted code KEY is a reserve word and so you need to escape it using "" double quote like below

INSERT INTO TBL_CONFIG("KEY",VALUE) 
VALUES ('1_default_meter_type_for_device_type_1','822') 
ON DUPLICATE KEY 
UPDATE "KEY"='1_default_meter_type_for_device_type_1', VALUE='822'

EDIT:

Totally confused. Oracle doesn't have ON Dulicate Key Update. You have to use MERGE statement as commented by Fred-ii.

Upvotes: 1

Zemistr
Zemistr

Reputation: 1049

Try this:

$sql = sprintf(
    "INSERT INTO $table_name %s %s ON DUPLICATE KEY UPDATE ",
    $this->prepare_insert_sql("", $fields, false),
    $this->prepare_insert_sql(" VALUES ", $values, true)
);

for($index = 0; $index < count($fields); $index++) {
    if($index > 0) {
        $sql .= ", ";
    }

    // added " before and after field name
    $sql .= '"' . $fields[$index] . '"=\'' . $values[$index] . "'";
}

Upvotes: 0

Related Questions