SQLIte Update multiple columns at once

[lsakizada]

Hi,

I am new to sqlite and need some help...

is there a simple way to update sqlite columns at once, e.g. updating many columns at single execution such as this

$Data = "Data" 

_SQLite_Exec($hDB, "UPDATE mytable set mycolumn = " & _SQLite_FastEscape($Data) & " where ID=1")

But instead of updating single column I want to update as many as I need .

Thanks

Edited April 26, 2014 by lsakizada

[MHz]

The WHERE clause sets the condition of what get updated. You have ID=1 which is just UPDATE in the ID column that is 1. If you want to update mycolumn which has 'egg' in that column with the value of $Data then you would change the where clause to this.

$Data = "Data" 

_SQLite_Exec($hDB, "UPDATE mytable set mycolumn = " & _SQLite_FastEscape($Data) & " where mycolumn = 'egg'")

If there is multiple instances of 'egg' in mycolumn then each instance of 'egg' would be updated. Look here about information using UPDATE. Perhaps take a look at the expressions page as well.

[jchd]

In complement to what MHz said, you can as well update more than one column at once:

update mytable set col1 = 'this', col2 = 'that', col3 = 123456 ... where <condition>

[lsakizada]

Hi MHZ and jchd,

thanks for the reply unfortunately my issue is not resolved yet.

Part of my application is storing user preferences of mail server such as 'MailServer', 'FromAddress', 'ToAddress', etc.

the application form came initially empty and populated by the user, so my needs are:

1) Create a table (need only single raw)

2) Insert a single row with only some initial data into it such as Id=1 and Port=25

3) when the user save data into the form the table row is updated.

Also,

Each time the user launch the application there is attempt to create that table if not exists

if table not exists then steps 1 and 2 above will run. and If table exists, then do not run steps 2 (remember,the case is when launching the application).

So, here is what I have done so far for step 1 and 2:

_SQLite_Exec($hDB, "CREATE TABLE if not exists Automation_Mail (ID,SmtpServer,FromName,FromAddress,ToAddress,Subject,Body,AttachFiles,CcAddress,BccAddress,Importance,Username,Password,IPPort,SSL,MailOnStart,MailOnFinish,EnableMail);")
_SQLite_Exec($hDB,"INSERT INTO Automation_Mail ('1','SmtpServer','FromName','FromAddress','ToAddress','Subject','Body','AttachFiles','CcAddress','BccAddress','Importance','Username','Password','IPPort','SSL','MailOnStart','MailOnFinish','EnableMail') SELECT ('ID') WHERE NOT EXISTS(SELECT 1 FROM Automation_Mail WHERE ID = 1)")

For step 3 (Update when user save preferences)

_SQLite_Exec(-1, "BEGIN TRANSACTION")
_SQLite_Exec($hDB, "UPDATE Automation_Mail set SmtpServer = " & _SQLite_FastEscape($SmtpServer) & " where ID=1")
_SQLite_Exec(-1, "COMMIT TRANSACTION")

I am getting sqlite error on running step 2 (insert values) "INSERT INTO Automation_Mail..." and step 3 obviously not done.

Edited April 26, 2014 by lsakizada

[jchd]

You're making several mistakes here.

First declare types in the create statement: I'd guess that ID is an meaningless integral identifier, make it ID integer not null primary key

Then use SQLite types as needed: TEXT or INT or whatelse.

Your insert is incorrect. You should refer to the SQLite "railroad" diagrams which expose the expected syntax of SQL statements. Note that schema names (e.g. column names) can be enclosed in double quotes "first column", in squre brakets [second column] or not enclosed at all if the name doesn't contain whitespaces.

So the first part of your insert should be:

INSERT INTO Automation_Mail (ID, SmtpServer, FromName, FromAddress, ToAddress, Subject, Body, AttachFiles, CcAddress, BccAddress, Importance, Username, Password, IPPort, SSL, MailOnStart, MailOnFinish, EnableMail)

Also if you insert values for every column defined in the table, you don't need to enumerate them again:

insert into Automation_Mail values (...) where ...

In all cases you MUST provide as many values as listed or implied in the first part of the insert statement.

Finally there is no point for a where clause in an insert statement: that is illegal syntax.