Thank you for your elaborative clarification.
So to handle this kind of requirement , what alternative we have in jOOQ ?
i.e. any other approaches/work-a-rounds ?
Regards,
~Shyam
On Wednesday, February 5, 2014 3:37:35 PM UTC+5:30, Lukas Eder wrote:
>
> Hi Shyam,
>
> This query
>
> int count = ctx.update(cn).set(cn.field("already_exists"), 1)
>> .where( (cn.field("ID")).in( ) ).execute();
>
>
> Renders this statement:
>
> UPDATE
>> (SELECT [cd].[HC1_LEAF_ID],
>> [cd].[HC2_LEAF_ID],
>> [cd].[HC3_LEAF_ID],
>> [cd].[PLANE_ID],
>> ? [already_exists],
>> row_number() over (order by [cd].[HC1_LEAF_ID] ASC) [ID]
>> FROM [DBO].[CATALOG_DETAIL] [cd]
>> WHERE [cd].[IS_PHANTOM] = ?
>> ) [cn]
>> SET [cn].[already_exists] = ?
>> WHERE 1 = 0
>
>
> So, the error message you're getting:
>
> *It is giving error "Incorrect syntax near '('."*
>
>
> simply indicates that you cannot update derived tables in SQL Server
> (unlike Oracle). This can be seen here:
> http://technet.microsoft.com/de-de/library/ms177523.aspx
>
> T-SQL doesn't allow derived tables:
>
> UPDATE
> [ TOP ( expression ) [ PERCENT ] ]
> { { table_alias | <object> | rowset_function_limited
> [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
> }
> | @table_variable
> }
>
>
> It would have been great help if it is displayed a proper log in which
>> line number or character the error occurred.
>
>
> This is a SQL Server error message. jOOQ currently does not add any
> additional parser layer in-between to help you assess what kinds of syntax
> errors your SQL statement is producing.
>
> In the exception stack trace I see "*[cn] set [cn].[already_exists] = ?
>> where 1 = 0*" .
>> I dont know why there is a condition "where 1=0" interpretation here.
>> Totally confusing.
>>
>
> This is an emulation of your empty IN predicate
>
> (cn.field("ID")).in( )
>
>
> In most SQL dialects, unfortunately, you cannot leave this predicate empty
> as you did above, so the same is emulated using 1 = 0, which is the most
> sensible interpretation of an empty IN predicate.
>
>
> General remark:
>
> I understand that what you're trying to do here is updating what you think
> is an emulation of a T-SQL temporary table. This attempt won't make any
> sense, because this emulation that I gave you was only valid for *that
> very specific* use-case. I tried to warn you several times that you
> should stop insisting on these T-SQL temporary tables. Even if that is a
> requirement for you, that requirement is maybe simply wrong.
>
> You simply cannot update this table. What sense would it make to update
> this bind variable, for instance? Where would T-SQL write this update to?
> This is like writing to /dev/null :-)
>
> UPDATE
> (SELECT [cd].[HC1_LEAF_ID],
> [cd].[HC2_LEAF_ID],
> [cd].[HC3_LEAF_ID],
> [cd].[PLANE_ID],
> ? [already_exists],
> row_number() over (order by [cd].[HC1_LEAF_ID] ASC) [ID]
> FROM [DBO].[CATALOG_DETAIL] [cd]
> WHERE [cd].[IS_PHANTOM] = ?
> ) [cn]
> SET [cn].[already_exists] = ?
> WHERE 1 = 0
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
