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.
