SQLite SQL question
lazarus / 16-Databases

----------------------------------------------------------------------
JanRoza · 2010-01-08 23:06

I want to update one table with some data from another table and tried
to use the following solution:

Code: [Select] 
  strSQL := 'UPDATE Vluchten SET Type = (SELECT Type FROM Registraties
WHERE Registratie = "' + DB_Registratie.Text + '") WHERE (ID = "' +
strId + '");';
  tblVluchten.SQL := strSQL;
  tblVluchten.ExecSQL;

Problem is that the result of the SELECT statement is a string so it
should be enclosed with quotes, but how to do that?
If I change strSQL to:

Code: [Select]'UPDATE Vluchten SET Type = "(SELECT Type FROM
Registraties WHERE Registratie = "' + DB_Registratie.Text + '")" WHERE
(ID = "' + strId + '");'; 

then the SELECT statement is seen as text and not as an SQL statement.
So does anyone know how to get my UPDATE SQL working?


Jan

----------------------------------------------------------------------
Zaher · 2010-01-08 23:42

QuoteSELECT statement is a string so it should be enclosed with quotes
No, it is not constant value,
I think you have problem around get multiple values, try use LIMIT.
Try to test the select before mix it.

Also you can use Params instead of add the string.

----------------------------------------------------------------------
LuizAmérico · 2010-01-09 15:18

Try quote function.

See http://www.sqlite.org/lang_corefunc.html

----------------------------------------------------------------------
JanRoza · 2010-01-09 22:11

Zaher: I know for sure I get only one value from the SELECT statement
as table B only has unique registrations.

Luiz: I think that might work but can you give me an example how to
use a function in SQL?
Never used functions before, is it like this?:

Code: [Select]strSQL := 'UPDATE Vluchten SET Type = QUOTE(SELECT Type
FROM Registraties WHERE Registratie = "' + DB_Registratie.Text + '")
WHERE (ID = "' + strId + '");';

Tried this but it gave a SQLite error message EdatabaseError - syntax
error.

----------------------------------------------------------------------
alter · 2010-01-09 22:58

Quote from: JanRoza on January 09, 2010, 11:11:00 pmZaher: I know for
sure I get only one value from the SELECT statement as table B only
has unique registrations.

Luiz: I think that might work but can you give me an example how to
use a function in SQL?
Never used functions before, is it like this?:

Code: [Select]strSQL := 'UPDATE Vluchten SET Type = QUOTE(SELECT Type
FROM Registraties WHERE Registratie = "' + DB_Registratie.Text + '")
WHERE (ID = "' + strId + '");';

Tried this but it gave a SQLite error message EdatabaseError - syntax
error.
http://marc.info/?l=sqlite-users&m=111203844702519&w=2

----------------------------------------------------------------------
LuizAmérico · 2010-01-10 01:41

Try QUOTE((SELECT Type FROM Registraties WHERE Registratie = "' +
DB_Registratie.Text + '"))

----------------------------------------------------------------------
JanRoza · 2010-01-10 19:04

Thanks Luiz, those double brackets do the trick.

----------------------------------------------------------------------
Zaher · 2010-01-12 07:53

Hmm, That strange using QUOTE for result of subquery

i test a similar query without quote in my data and have no problem
Code: [Select]update groups set
grpname = (select catname from categories where catid =1)
where grpid = 1

What the error you get, what the version of sqlite you are use?

----------------------------------------------------------------------
JanRoza · 2010-01-12 12:35

Zaher, I'm using the latest SQLite version.
The original problem is not that the query -technically - does not
work but the result was not updated in the vchar field Type.
In itself this is logical as in an assignment to a vchar field a found
value like PH-1450 is not seen as a string, so I needed quotes around
it.
But placing quotes around the SELECT statement resulted in the whole
SELECT statement being interpreted as a string (and not being
executed), so that's why I wanted the QUOTE function.

----------------------------------------------------------------------
Zaher · 2010-01-12 16:18

In my example i also assigned id to the name and it worked
But if you need to make it in standard, i prefer use CAST instead of
QUOTE

Type = cast((sele...) as VARCHAR(60) )

----------------------------------------------------------------------
JanRoza · 2010-01-12 22:39

Didn't know the CAST function, I need to delve into those function a
bit more.
Until now I did most work using the tables methods (edit, post, etc.)
or simple SQL but those function really make life a lot easier.