Help whith SQLite change/update single field

[BigyBG]

Can someone explain to me how to change or update entries in a single field from an existing table?

Thank you in advance for the help

[JoHanatCent]

Use _SQLite_Exec with "UPDATE"

[Myicq]

If you need to update a field based on a key value (typically in col. 1), you could write your own little UDF to do the job. Either by hard coding the field name or by specifying the field name as a variable. Depends on your needs

So you could call the function like this

; _SQLiteUpdate($id, $field, $newvalue)
_SQLiteUpdate(1703, size, "L")       ; change field size to value L for id=1703

The SQL sentence inside must be something like

UPDATE table set $field = $newvalue where idfield=$id

It is a good idea to quote things in SQLite, so values are put between ' and '

[BartW]

i use this

this function wil find a value in a colom and then change a valua in a colom on the sam row

update functie
; zoekt de opgegeven waarde in het opgegeven tabel en verander een waarde in de opgegeven colum
; gebruik _sqlite_update( $hDB,$sTable,$ColumFind,$sValueFind,$ColumChange,$sValueChange)
; $hDB = An open database, use -1 To use last opened database
; $sTable = The Name Of the Tabel to be search
; $ColumFind = The Name Of the Colum to be search in the Tabble
; $sValueFind = The Value to be search In the Colum
; $ColumChange = The to change the colum in the Tabble
; $sValueChange = The to change the Value  in the Colum
 
func _SQLite_update( $hDB,$sTable,$sColumFind,$sValueFind,$sColumChange,$sValueChange)
 
$SQLFINDSTRING = "UPDATE "&$sTable&" SET "&$sColumChange&" = '"&$sValueChange&"' WHERE "&$sColumFind&" = '"&$sValueFind&"';"
_SQLite_Exec ($hDB,$SQLFINDSTRING)
 
EndFunc

Edited November 1, 2011 by BartW

[jchd]

@BartW, OP

Don't simply surround litteral text values with single quotes: that's not enough to protect you from SQL errors at runtime.

Why? Because if a litteral itself contains a single quote, it will terminate the litteral. Embedded single quotes should be doubled.

UPDATE mytable set mycolumn = 'O'Connor' where ... <--- Syntax error after 'O'

UPDATE mytable set mycolumn = 'O''Connor' where ... <--- will work

You should wrap your string litterals using _SQLite_Escape (or the much faster _SQLite_Fast_Escape() offered with the last beta).

For brevity, I use the following equivalent:

Func X($sValue)
  Return(StringReplace($sValue, "'", "''"))
EndFunc

And then:

$sName = "O'Connor"
_SQLite_Exec($hDB, "UPDATE mytable set mycolumn = " & X($sName) & " where ...")

BTW doing so also protects you from SQL injection attacks, even if such attack is improbable in an embedded DB context. Anyway, it's a sane habit to grab.

While I'm at it, you must know you need to use square brackets around schema names when they may otherwise lead to SQL error:

SELECT [isn't that a strange name for a ÁÇÌþæ÷Ĉ column?] FROM [my table has a long name with spaces] WHERE [where] LIKE 'abc%'

SQLite also accepts double quotes instead of square brackets:

SELECT "Isn't that a strange name for a ÁÇÌþæ÷Ĉ column?" FROM "my table has a long name with spaces" WHERE "where" LIKE 'abc%'

While SQLite will do its best to disambiguate wrongly quoted statements, you should avoid mixing roles of single- and double-quotes.

Edited November 1, 2011 by jchd

[BartW]

Thanx jchd

Hmm I must revise my scrips

well I learn something new every day

[BartW]

oke i think i got it now.

and fore the starter of this tropic .

http://www.sqlite.org/lang.html

func _SQLite_update( $hDB,$sTable,$sColumFind,$sValueFind,$sColumChange,$sValueChange)
 
$SQLFINDSTRING = "UPDATE ["&$sTable&"] SET ["&$sColumChange&"] = "&_SQLite_Escape($sValueChange)&" WHERE ["&$sColumFind&"] = "&_SQLite_Escape($sValueFind)&";"
_SQLite_Exec ($hDB,$SQLFINDSTRING)
 
EndFunc

Edited November 1, 2011 by BartW

[jchd]

Fine, even is that looks like a bit of a mouthful, it's rock solid this way.

In practice you often have to perform more complex updates of several columns, sub-selects with joins, more conditions, etc.

The practical way is to forget about fixed-form SQL statements and write them in the flow of your program. You know your tables and know which one need [ ] quoting, same with data: when you know you have a correctly formatted text date (e.g. 2010-10-31) you know you don't have to escape it since it will never contain a single quote. Of course it's obviously safer to always escape fields like persons' names, addresses, items descriptions, comments, ... as they might have one or more single quote embedded.