Jump to content

SQLIte Update multiple columns at once


Recommended Posts

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 by lsakizada

Be Green Now or Never (BGNN)!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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>

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to comment
Share on other sites

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 by lsakizada

Be Green Now or Never (BGNN)!

Link to comment
Share on other sites

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.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...