CODEWITHSUNDEEP
sqlite
Mark Tomlin
Mark Tomlin

Reputation: 8943

INSERT OR REPLACE WHERE ROWID Causes Error

The following code:

$stm = $sql->prepare('INSERT OR REPLACE INTO "vehicle_service_invoice" (
        invoice, "date", unit, odometer, sublet, sub, po, categories
    ) VALUES (
        :invoice, :date, :unit, :odometer, :sublet, :sub, :po, :categories
    ) WHERE rowid = :rowid;'
);
$stm->bindParam(':invoice', $_POST['invoice']);
$stm->bindParam(':date', $_POST['date']);
$stm->bindParam(':unit', $_POST['unit']);
$stm->bindParam(':odometer', $_POST['odometer']);
$stm->bindParam(':sublet', $_POST['sublet']);
$stm->bindParam(':sub', $_POST['sub']);
$stm->bindParam(':po', $_POST['po']);
$stm->bindParam(':categories', $categories);
$stm->bindParam(':rowid', $_POST['rowid']);
$stm->execute();

Produces the following query:

INSERT OR REPLACE INTO "vehicle_service_invoice" (
    invoice,
    "date",
    unit,
    odometer,
    sublet,
    sub,
    po,
    categories
) VALUES (
    7230,
    '2013-02-07',
    558,
    34863,
    0,
    0,
    1486347,
    5
) WHERE rowid = 1

That produces the following error:

ERROR: near "WHERE": syntax error.

What I am trying to do is make a single path for both my INSERT and UPDATE logic to follow, so after I found out that I could do INSERT OR REPLACE, I figured I could just update the information based on the ROWID of each item. To me the syntax looks correct, what am I doing wrong?

It should be noted that I don't care about changing ROWID values as I understand that is a tripping point on doing INSERT OR REPLACE statements. Everything is joined together in other queries based off of the INVOICE column. I only want to use the ROWID to refer to that row.

Upvotes: 0

Views: 532

Answers (1)

CL.
CL.

Reputation: 180070

In an INSERT statement, a WHERE clause does not make sense.

The INSERT OR REPLACE works as follows: a record with the specified values is inserted into the table. If this would result in a UNIQUE constraint violation, the old record is deleted.

To replace a record that might already exist, the colum(s) that identify that record must be part of the values to be inserted. In other words, you must insert the rowid value:

INSERT OR REPLACE INTO vehicle_service_invoice(rowid, ...) VALUES (1, ...)

Upvotes: 1

Related Questions