Problem with Command LIMIT in query of tDBRow for SQLite

[Anonymous]

Hello,

I have a problem with the word LIMIT in the Query of the tDBRow (for SQLite):

For example, I have the following Query:

"
UPDATE DOL
SET BlockedQuantityTotal = 200
WHERE IDG = '" + row3.IDG + "'
LIMIT 1
"

DOL is the Table,

When the Job is ran, I have the error: "near "LIMIT": syntax error"

I run this command in a SQLite Tool (for example SQLiteSpy) and this command works.

Could you pleas help me?

Thanks in advance...

[vapukov]

hi,

as I know UPDATE and DELETE with LIMIT supported not by all sqlite (it must be defined as option when build) - SQLITE_ENABLE_UPDATE_DELETE_LIMIT

second,  what need to check - what in row3.IDG?

as I can guess it is string (char/varchar), but what possible values and what final SQL code will be in this case?

[Anonymous]

First, Thanks for ypur answer.

Then, I'm not an expert with Talend, SQLite and SQL (sorry), where do I have to put this Option? In the Query "box" or somewhere else?

And, the Command I have written:

"
UPDATE DOL
SET BlockedQuantityTotal = 200
WHERE IDG = '" + row3.IDG + "'
LIMIT 1
"

Was an example and yes row3.IDG is a String.

what I want is to Update my Table only on the first row result of the query.

[vapukov]

this option must be used when sqlite driver compiled, and no any settings in Talend for this

try next:

"
UPDATE DOL
SET BlockedQuantityTotal = 200
WHERE IDG IN (SELECT IDG FROM DOL WHERE IDG = '" + row3.IDG + "' LIMIT 1)
"

[Anonymous]

Hello Vapukov,

Thanks for your answer but the result is that all the line with the row3.IDG are updated not only the first one.

I don't know why it doesn't not only update the first one.

I Have tried the following commnd:

"
UPDATE DOL
SET BlockedQuantityTotal = 200
WHERE id = (SELECT MIN(id) FROM DOL WHERE
IDG = '" + row3.IDG + "')
"

id is the column withe the unique auto-incremented key for each row of the table.

This seems to work.

[vapukov · Accepted solution]

you absolutely right - first SQL construction is wrong

case with MIN(id) is proper, 

with original query - it update ALL record with same IDG 🙂

[Anonymous]

Thanks for your support...

[vapukov]

welcome to the community! 🙂