[SOLVED] SQLite3: Update One Table with Another
lazarus / 16-Databases

----------------------------------------------------------------------
guest48180 · 2014-11-19 03:12

I've really been fighting this. I have two tables. Well, I have five,
but these are the two in question.

Code: [Select]sqlite> .schema attendee_owes
CREATE TABLE ATTENDEE_OWES(
                OWES_ID INTEGER PRIMARY KEY,
                BALANCE REAL NOT NULL,
                FK_OWES_ID INTEGER,
                FOREIGN KEY(FK_OWES_ID) REFERENCES
ATTENDEES(ATTENDEE_ID)
);
sqlite> .schema balance
CREATE TABLE BALANCE(
                BALANCE_ID INTEGER PRIMARY KEY,
                BALANCE_AMT REAL NOT NULL
);

My query to update BALANCE_AMT is:

Code: [Select]mainForm.Query3.Close;
mainForm.Query3.SQL.Text:= 'UPDATE BALANCE SET BALANCE_AMT =
TOTAL(BALANCE) FROM ATTENDEE_OWES;
mainForm.Query3.ExecSQL; 

And it throws this error:

Code: [Select]A Database Error Occured. Technical Message: near
"ATTENDEE_OWES": syntax error
I'm assuming that, since Query3 is tied to the BALANCE table, it
doesn't see the ATTENDEE_OWES table, which is tied to Query4. But what
to do? I've tied this every way I can think of, but nothing works. And
I want to UPDATE the BALANCE_AMT, not repeatedly insert a new value
every time. Isn't this possible, updating a single value with the
total of others? I got the SQLite code from:
http://zetcode.com/db/sqlite/datamanipulation/ and
http://www.tutorialspoint.com/sqlite/sqlite_update_query.htm - but
they don't show how to get a total value from another table like I'm
trying to do.

I'm out of my league on this one and need some help.

Thanks,
Landslyde

----------------------------------------------------------------------
kpeters58 · 2014-11-19 04:15

Always a good idea to go to the source to see what syntax is
supported:

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

What you can do (very contrived - just so you understand the syntax):

update customers set name = (select name from staff where id = 1)
where id = 44

----------------------------------------------------------------------
guest48180 · 2014-11-19 04:48

Thanks, kpeters58.  But the issue arises when I try to use the TOTAL
of all values from one table to update a single value in another
table, and the code fails due to that. Query3 is set for the BALANCE
table, not for the ATTENDEE_OWES table. So it throws this particular
error because that table isn't recognized. But there's a way to do
this. I'm sure I'm not the only one who has ever tried to do this very
thing. But thanks for your input.

Landslyde

----------------------------------------------------------------------
kpeters58 · 2014-11-19 04:55

You are not using this syntax at all - you are using illegal syntax as
stated by you:

Code: [Select]mainForm.Query3.SQL.Text:= 'UPDATE BALANCE SET
BALANCE_AMT = TOTAL(BALANCE) FROM ATTENDEE_OWES;
Please compare my example with with you posted - the difference should
be obvious!

----------------------------------------------------------------------
guest48180 · 2014-11-19 05:10

kpeters58:
This statement, like the one in my original post, works perfectly.

Code: [Select]mainForm.Query3.Close;
mainForm.Query3.SQL.Text:= 'UPDATE ATTENDEE_OWES SET BALANCE = :r
WHERE FK_OWES_ID = :s';
mainForm.Query3.Params.ParamByName('r').AsFloat:=
mainForm.Query2.Params.ParamByName('h').Value;
mainForm.Query3.Params.ParamByName('s').AsInteger :=
mainForm.Query2.Params.ParamByName('i').Value;
mainForm.Query3.ExecSQL;

The only difference is that I'm not trying to access a second table in
it. I'm trying to use the aggregate function TOTAL with the values of
a column in another table. I see no way to rewrite my query from my
original post to coincide with what you gave me...and it work. Sorry.
But again, thanks for your input.

Landslyde

UPDATE: I did try it your way:

Code: [Select]mainForm.Query3.Close;
mainForm.Query3.SQL.Text:= 'UPDATE BALANCE SELECT FROM ATTENDEE_OWES
TOTAL(BALANCE)';
//mainForm.Query3.SQL.Text:= 'UPDATE BALANCE SET BALANCE_AMT = TOTAL
ATTENDEE_OWES.BALANCE';
mainForm.Query3.ExecSQL;

But that gave me an error with the SELECT statement. So I tried this:

Code: [Select]mainForm.Query3.Close;
mainForm.Query3.SQL.Text:= 'UPDATE BALANCE (SELECT FROM ATTENDEE_OWES
TOTAL(BALANCE))';
//mainForm.Query3.SQL.Text:= 'UPDATE BALANCE SET BALANCE_AMT = TOTAL
ATTENDEE_OWES.BALANCE';
mainForm.Query3.ExecSQL;  

...and it didn't like the '('

----------------------------------------------------------------------
guest48180 · 2014-11-19 06:55

This works:

Code: [Select]mainForm.Query3.Close;
mainForm.Query3.SQL.Text:= 'DELETE FROM BALANCE';
mainForm.Query3.ExecSQL;
mainForm.Query3.Close;
mainForm.Query3.SQL.Text:= 'INSERT INTO BALANCE(BALANCE_AMT) SELECT
TOTAL(BALANCE) FROM ATTENDEE_OWES';
mainForm.Query3.ExecSQL;  

And although I haven't tried it, this might work as well...which would
be very similar to what kpeters58 was talking about:

Code: [Select]mainForm.Query3.SQL.Text:= 'UPDATE BALANCE(BALANCE_AMT)
SELECT TOTAL(BALANCE) FROM ATTENDEE_OWES';

Landslyde