SQL Replace or Insert Into Syntax error

Question

I cannot figure out what is wrong. My query looks like this:

INSERT OR REPLACE INTO lang (name, string, lang) VALUES ('PAGE_TITLE', 'Page Title', 'en')

And this is where I execute that query:

foreach($lang_fields as $field){
    $update = "INSERT OR REPLACE INTO lang (name, string, lang) 
            VALUES ('".mysqli_real_escape_string($conn, $field)."', 
                    '".mysqli_real_escape_string($conn, $_POST[$field])."', 
                    '".mysqli_real_escape_string($conn, $_POST['lang_load'])."')";
    echo $update;
    $sql = mysqli_query($conn, $update)
        or die(mysqli_error($conn));
}

And this is the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OR REPLACE INTO lang (name, string, lang) VALUES ('PAGE_TITLE', 'P' at line 1

There is no INSERT OR REPLACE INTO they are separate statements dev.mysql.com/doc/refman/5.6/en/insert.html and dev.mysql.com/doc/refman/5.6/en/replace.html — Uueerdo
– Uueerdo, Commented Jul 7, 2015 at 19:33

AdamMc331 · Accepted Answer · 2015-07-07 20:07:49Z

3

MySQL does not support any INSERT OR REPLACE INTO syntax that I've seen. You may be looking for something like INSERT INTO ... ON DUPLICATE KEY UPDATE. In your example it may look like this:

INSERT INTO myTable (name, string, lang) VALUES (param1, param2, param3)
ON DUPLICATE KEY UPDATE name = param1, string = param2, lang = param3;

More information can be found in the documentation here: https://dev.mysql.com/doc/refman/5.0/en/insert.html

An example of this can be found using this SQL Fiddle.

edited Jul 7, 2015 at 20:07

answered Jul 7, 2015 at 19:36

AdamMc331

16.8k12 gold badges79 silver badges134 bronze badges

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

14 Comments

TemporaryName Over a year ago

Yes, I was looking at SQLite resources, it seems that MySQL does not support this. Thank you.

AdamMc331 Over a year ago

@TemporaryName no problem. If this answer works for you, feel free to upvote and accept the answer. If you have trouble getting it working just ask.

TemporaryName Over a year ago

I'm working on implementing this. The code just inserted 2000 rows into my table, it probably didn't detect the duplicate key. Any idea why?

AdamMc331 Over a year ago

Which column is your key? Are you sure there are duplicates?

TemporaryName Over a year ago

I solved it with your answer and a bit more php to get the id.

|

Collectives™ on Stack Overflow

SQL Replace or Insert Into Syntax error

1 Answer 1

14 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related