SQLite Insert or Replace

Unity Engine
1

Just curious how to impliment INSERT OR REPLACE via sqlite? I am currently trying

cmd.CommandText = "REPLACE INTO tblMASTER(PID,NAME,VALUE) VALUES ('1', 'Name', 'Value') WHERE PID='1';";
cmd.ExecuteNonQuery();

and I keep getting “SQLite Error” yet my insert function works:

cmd.CommandText = "INSERT INTO tblMASTER(PID,NAME,VALUE) VALUES ('1', 'Name', 'Value');";
cmd.ExecuteNonQuery();

Any ideas?

2

So has anyone tried REPLACE or UPDATE in unity? I cant seem to get anything to work.

The only things that seem to work are INSERT and SELECT.

What am I doing wrong? (doing this in C#)

3

try deleting the quotes in WHERE PID=‘1’

4

When I take out the “where” command the INSERT OR REPLACE workes … so it looks like this

cmd.CommandText = "INSERT OR REPLACE INTO tblVALVES (PID, NAME, VALUE, UNIT, LOCATION) VALUES ('1', 'ValveName', 'true', 'UnitHere', 'LocationHere');";

But when I add a WHERE command it stops working. I tried:

WHERE PID = 1);";
WHERE PID = '1');";
WHERE PID=1);";
WHERE PID='1');";
WHERE 'PID = 1');";
WHERE (PID = 1));";

Nothing seems to work.

5

Apparently the UPDATE works :

"UPDATE tblVALVES SET PID='2', NAME='ValveName', VALUE='true', UNIT='UnitHere', LOCATION='LocationHere' WHERE PID = '1';";

So Now I have INSERT, SELECT, and UPDATE working. It would be nice if ‘INSERT OR REPLACE’ worked but I guess I can work with these for now.

6

I am new to game development, but have been working on Real time systems in banks etc using SQL for a over 15 years, and worked on DB systems like Oracle, SQL Server and MySQL mainly.

The SQL Statement “Insert or Replace” cannot have a where clause to my knowledge. That is the reason why you got the warning, but it still parses and executes. To make your statement work, you need to look at your primary keys, ie. the unique indices on your table. The statement will use that as reference to either insert a new row, or replace an existing row.

For example, if you have a table structure like below:-
A - Unique ID
B - String

And you have the following inserted:-
1, Hello
2, Pete
3, Dog

If you then run your statement as Insert or Replace Into Table values (2, ‘Doh!’)

It will replace ‘Pete’ with the string ‘Doh!’

I hope this is of some help.