I have a basic Windows Forms application that connects and displays databases. I want to update one of the databases using information from two tables,
UPDATE account AS a
SET accrued = (a.accrued + ((p.intrate/365)*balance))
FROM customer c JOIN product p
ON p.prodid = a.prodid
WHERE c.custid = a.custid AND active = 1
That works in DB browser
using (SQLiteCommand cmd = connAccount.CreateCommand())
{
// adds customers details to the database
cmd.CommandText = @"UPDATE account AS a SET accrued = (a.accrued + ((p.intrate / 365) * balance)) FROM customer c JOIN product p ON p.prodid = a.prodid WHERE c.custid = a.custid AND active = 1";
cmd.ExecuteNonQuery();
MessageBox.Show("Daily Accrued Updated");
}
That in my application gives me the error:
System.Data.SQLite.SQLiteException: 'SQL logic error near "FROM": syntax error'
The syntax of the UPDATE...FROM statement in your code is not supported for versions of SQLite older than 3.33.0.
You can use older syntax with a correlated subquery:
UPDATE account AS a
SET accrued = a.accrued +
(SELECT p.intrate/365
FROM customer c JOIN product p
ON p.prodid = a.prodid
WHERE c.custid = a.custid) * a.balance
WHERE a.active = 1