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

Question

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.

`key`, far as I know is a reserved word in MySQL. Whether it's the same for Oracle, that I'd have to Google. It would need to be wrapped in backticks. — Funk Forty Niner, Jul 04 '14 at 13:38
It's confirmed, `key` is an Oracle reserved keyword also. http://docs.oracle.com/cd/B10501_01/appdev.920/a42525/apb.htm - Either give it another name, or wrap it in backticks `\`` — Funk Forty Niner, Jul 04 '14 at 13:43
Still got the same error. Seems like oracle doesn't have `on duplicate ` condition — FreshPro, Jul 04 '14 at 13:45
That I couldn't help you out with. I don't know Oracle. Try and wrap the `%s %s` with quotes. — Funk Forty Niner, Jul 04 '14 at 13:46
It does have one http://docs.oracle.com/cd/E17952_01/refman-5.0-en/insert-on-duplicate.html — Funk Forty Niner, Jul 04 '14 at 13:49
MySQL is bought by Oracle :) Look at the link it's for mysql 5.0 — FreshPro, Jul 04 '14 at 13:51
See this answer http://stackoverflow.com/a/17254072/ - Use `MERGE` instead. http://stackoverflow.com/a/17946783/ — Funk Forty Niner, Jul 04 '14 at 13:54

score 2 · Accepted Answer · answered Jul 04 '14 at 14:03

2

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')

answered Jul 04 '14 at 14:03

FreshPro

875
3
14
35

Right on, glad to hear it. – Funk Forty Niner Jul 04 '14 at 14:04
1

I appriciate all the help – FreshPro Jul 04 '14 at 14:05

Rahul · Answer 2 · 2014-07-04T14:01:58.290

1

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.

edited Jul 04 '14 at 14:01

answered Jul 04 '14 at 13:46

Rahul

76,197
13
71
125

So it's quotes and not backticks then. Yet, that would need to be done in OP's query. `$sql = sprintf("INSERT INTO $table_name %s %s ON DUPLICATE KEY UPDATE ",...` - What the OP shown us in the 2nd part, is what the query's results are. – Funk Forty Niner Jul 04 '14 at 13:47
Still, I get same error. I belive it's `on duplicate key` part – FreshPro Jul 04 '14 at 13:48
@Fred-ii-, It's Oracle and so you need to use `"` to escape reserve word. – Rahul Jul 04 '14 at 13:50
OP needs to use `MERGE` http://stackoverflow.com/a/17946783/ - http://stackoverflow.com/a/17254072/ *"Oracle doesn't have on duplicate key update"* – Funk Forty Niner Jul 04 '14 at 13:55
That I couldn't say for sure. I don't know Oracle, but I do know "Google" ;-) – Funk Forty Niner Jul 04 '14 at 13:58
`on duplicate key` is a no go on oracle unlike mysql – FreshPro Jul 04 '14 at 14:00
*"as commented by others"* - lol as found by Fred ;-) I'm just kidding. – Funk Forty Niner Jul 04 '14 at 14:03
Thank for the mention Rahul :) *Cheers* Glad we were able to help the OP. – Funk Forty Niner Jul 04 '14 at 14:06

Zemistr · Answer 3 · 2014-07-04T13:48:21.770

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] . "'";
}

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

3 Answers3