UPDATE multiple variables in SQLite?

Getting Started Newbie Questions
#1

Good Day to All!

I have scoured the forums world wide and can not find an example showing multiple variables used in an SQLite  UPDATE statement.  The following uses one variable(shooter) and I need to use more than one, but not necessarily all columns in the shooters table.  Would appreciate anyone’s assistance!

local shooter = “Tom”

local isOwner = “yes”

– this works for a single variable. What about multiples???

local updateData = [[UPDATE shooters SET name =’]]… shooter … [[’ WHERE owner=’]]… isOwner …[[’;]]

db:exec( updateData )

#2

See:  http://www.tutorialspoint.com/sqlite/sqlite_update_query.htm

UPDATE table SET key1 = value1, key2 = value2, key3 = value3 WHERE column=someValue;

Rob

#3

Thanks, Rob.  I’m familiar with the SQLite statement. I just can’t seem to get it to work in Corona/lua.  As I mentioned it works with a single variable.  BTW, which is better to use? [[ or " ?  Thanks for your help!

#4

Hi rconnor,

I usually prefer something like:

[lua]local query = string.format( “UPDATE ‘%s’ SET key1=’%s’, key2=’%s’, key3=’%s’ WHERE column=’%s’;”, “myTableName”, “val1”, “val2”, “val3”, “myColName” )[/lua]

Use %s if you’re expecting a string value

Use %d is you’re expecting a numerical value

Additionally, when using string values, make sure they are properly encoded for the database.

http://www.lua.org/manual/5.1/manual.html#pdf-string.format

Cheers.

#5

Thanks develephant!  I’m gonna check out your version. I have this working well.

local updateData =“UPDATE myTable SET key1 = '”… val1 …"’, key2 = ‘" … val2 … "’, key3 = ‘" … val3 … "’, key4 = ‘" … val4 … "’ WHERE column = ‘" … myColName… "’";

#6

See:  http://www.tutorialspoint.com/sqlite/sqlite_update_query.htm

UPDATE table SET key1 = value1, key2 = value2, key3 = value3 WHERE column=someValue;

Rob

#7

Thanks, Rob.  I’m familiar with the SQLite statement. I just can’t seem to get it to work in Corona/lua.  As I mentioned it works with a single variable.  BTW, which is better to use? [[ or " ?  Thanks for your help!

#8

Hi rconnor,

I usually prefer something like:

[lua]local query = string.format( “UPDATE ‘%s’ SET key1=’%s’, key2=’%s’, key3=’%s’ WHERE column=’%s’;”, “myTableName”, “val1”, “val2”, “val3”, “myColName” )[/lua]

Use %s if you’re expecting a string value

Use %d is you’re expecting a numerical value

Additionally, when using string values, make sure they are properly encoded for the database.

http://www.lua.org/manual/5.1/manual.html#pdf-string.format

Cheers.

#9

Thanks develephant!  I’m gonna check out your version. I have this working well.

local updateData =“UPDATE myTable SET key1 = '”… val1 …"’, key2 = ‘" … val2 … "’, key3 = ‘" … val3 … "’, key4 = ‘" … val4 … "’ WHERE column = ‘" … myColName… "’";