How to use INSERT OR REPLACE correctly?

Question

I was originally looking for an INSERT OR UPDATE ability in SQLite but searches showed similar question askers being pointed to using INSERT OR REPLACE.

I'm obviously misunderstanding how this works and obviously I can't use WHERE as I'm getting the following SQLiteException...

08-12 01:38:22.973: ERROR/AndroidRuntime(29242): FATAL EXCEPTION: main 08-12 01:38:22.973: ERROR/AndroidRuntime(29242): android.database.sqlite.SQLiteException: near "WHERE": syntax error: INSERT OR REPLACE INTO SERVERS ( server_name, service_uri_mobile, service_uri_wifi, valid_ssids, username,password ) VALUES ( 'Default', 'http://myserver.com:8790/', 'http://192.168.1.1:8790/', '[]', 'admin', 'password') WHERE server_name='Default'

The SQL string I'm using is as follows...

String UpdateString = "INSERT OR REPLACE INTO SERVERS " + "(server_name,service_uri_mobile,service_uri_wifi,valid_ssids,username,password) VALUES ('" + locater.getServerName() + "','" + locater.getServiceUriMobile().toString() + "','" + locater.getServiceUriWifi().toString() + "','" + locater.getValidSsids().toString() + "','" + locater.getUsername() + "','" + locater.getPassword() + "') " + "WHERE server_name='" + locater.getServerName() + "'";

I've looked at this page explaining REPLACE but don't quite understand it. How would I re-write the above SQLite command and have it only try to replace the record where the server_name matches (i.e., the equivalent of a WHERE clause)?

Is that your exact SQL query? I swear I didn't delete the trailing ) on it if it is... — Jared Farrish
– Jared Farrish, Commented Aug 12, 2011 at 0:53
@Jared - yes it WAS my exact SQL and I realise what you mean - I've edited to add the closing bracket after locater.getPassword() but I'm getting the same error. Thanks for formatting the exception BTW. — Squonk
– Squonk, Commented Aug 12, 2011 at 1:00
@Jared: Yes but notice I've added the closing bracket to the error in my question. So..exact but now with a closing bracket after 'password' — Squonk
– Squonk, Commented Aug 12, 2011 at 1:07
I saw that. If REPLACE were the error, I would expect the error to occur there and not on the syntax error at the WHERE clause. — Jared Farrish
– Jared Farrish, Commented Aug 12, 2011 at 1:12

Jared Farrish · Accepted Answer · 2011-08-12 01:31:57Z

Looking at the documentation (and mirroring the comments below the question), REPLACE should be used when UPDATEing (or in other words changing) columns that are UNIQUE. To wit:

REPLACE

When a UNIQUE constraint violation occurs, the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally.

http://sqlite.org/lang_conflict.html

Thank you for your help - I don't do very much with SQL and it was simply a case of not understanding what the docs were trying to tell me. Cheers!
No problem. Thanks for the points; I'm not a SQL all-star, but sometimes I can figure out what's going on. It is a different animal at times.

Collectives™ on Stack Overflow

How to use INSERT OR REPLACE correctly?

1 Answer 1

2 Comments

Hot Network Questions