"SQL error or missing data type near WHERE syntax" when trying to UPDATE SQLite

Question

I got a problem and I can't find the error. It doesnt let me update my index and always says SQL error or missing data type near WHERE syntax. Everything else works. So I thought you guys could have a look at this:

Create Table: execute("CREATE TABLE Players (player TEXT, money DOUBLE, job TEXT, level INTEGER, exp DOUBLE)"); Works

Insert: DATABASE.execute("INSERT INTO Players VALUES ('" + player + "', " + money + ", '" + job.getJob() + "', " + job.getLevel() + ", " + job.getEXP() + ")"); Works

Update: DATABASE.execute("UPDATE Players WHERE player = '" + player.toLowerCase() + "' SET money = " + money + ", job = '" + job.getJob() + "', level = " + job.getLevel() + ", exp = " + job.getEXP()); Doesnt work

I am using the following values to Update:

player = gemaken
money = 100.0
job.getJob() = miner
level = 0
exp = 0.0

Thanks in advance!

Sincerely, Gemaken

First thing to do: stop building SQL like that. Use a PreparedStatement, and your SQL will be much easier to read... and you won't be inviting a SQL Injection attack — Jon Skeet
– Jon Skeet, Commented Aug 15, 2014 at 12:40

laalto · Accepted Answer · 2014-08-15 12:39:33Z

2

In SQL UPDATE, the SET part comes first and the WHERE part only later:

DATABASE.execute("UPDATE Players SET money = " + money + ", job = '" + job.getJob() + "', level = " + job.getLevel() + ", exp = " + job.getEXP()) + " WHERE player = '" + player.toLowerCase() + "';

Also, consider using parameters and prepared statements.

answered Aug 15, 2014 at 12:39

laalto

153k68 gold badges293 silver badges306 bronze badges

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

Comments

demongolem · Accepted Answer · 2014-08-15 13:29:55Z

0

Please try this one

Update: DATABASE.execute("UPDATE Players SET money = " + money + ", job = '" + job.getJob() + "', level = " + job.getLevel() + ", exp = " + job.getEXP() WHERE player = '" + player.toLowerCase());

edited Aug 15, 2014 at 13:29

demongolem

9,75436 gold badges97 silver badges107 bronze badges

answered Aug 15, 2014 at 12:55

Rahul Vaghela

2113 silver badges9 bronze badges

Comments

Hassan Abbasi · Accepted Answer · 2014-08-15 13:30:12Z

0

Reason behind your update query to give error is the incorrect syntax of the UPDATE query. Follow the syntax as in here or here

Your query should be like this:

DATABASE.execute("UPDATE Players SET money=" + money + ", job='" + job.getJob() + "', level=" + job.getLevel() + ", exp=" + job.getEXP() + " WHERE player='" + player.toLowerCase() + "'")

answered Aug 15, 2014 at 13:30

Hassan Abbasi

113 bronze badges

Collectives™ on Stack Overflow

"SQL error or missing data type near WHERE syntax" when trying to UPDATE SQLite

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related