topic Re: Problem with Command LIMIT in query of tDBRow for SQLite in Talend Studio

Problem with Command LIMIT in query of tDBRow for SQLite

Anonymous — Mon, 28 Jan 2019 10:27:10 GMT

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...

Re: Problem with Command LIMIT in query of tDBRow for SQLite

vapukov — Mon, 28 Jan 2019 11:18:48 GMT

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?

Re: Problem with Command LIMIT in query of tDBRow for SQLite

Anonymous — Mon, 28 Jan 2019 12:12:57 GMT

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.

Re: Problem with Command LIMIT in query of tDBRow for SQLite

vapukov — Mon, 28 Jan 2019 12:16:25 GMT

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)
"

Re: Problem with Command LIMIT in query of tDBRow for SQLite

Anonymous — Mon, 28 Jan 2019 12:48:30 GMT

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.

Re: Problem with Command LIMIT in query of tDBRow for SQLite

vapukov — Mon, 28 Jan 2019 13:06:11 GMT

you absolutely right - first SQL construction is wrong

case with MIN(id) is proper,

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

Re: Problem with Command LIMIT in query of tDBRow for SQLite

Anonymous — Mon, 28 Jan 2019 13:08:29 GMT

Thanks for your support...

Re: Problem with Command LIMIT in query of tDBRow for SQLite

vapukov — Mon, 28 Jan 2019 13:09:48 GMT

welcome to the community! 🙂