-
-
Notifications
You must be signed in to change notification settings - Fork 1.4k
-
I use the ccjsqlparserutil.parse method in jsqlparser to directly report an error,
Oracle Database my sql is like this:
update taba s set (s.appname) = (select p.insuname from tableb p where p.insurednum ='3701020000008819' group by p.insuname),s.maskcode='P30' where s.pffno = '2671267';
it can be used in plsql,but use this jar will reprot error:
Encountered unexpected token: ")" ")"
at line 1, column 91.
Was expecting one of:
"&"
","...................
I think it's the set a=(),b the ',' can't be explan
I use maven jar version is 4.0,
like this:
com.github.jsqlparser
jsqlparser
4.0
Beta Was this translation helpful? Give feedback.
All reactions
Replies: 2 comments 6 replies
-
Greetings.
the problem are the extra-brackets around the column name (s.appname)=
Without the extra brackets your statement will be parsed:
UPDATE taba s SET s.appname = ( SELECT p.insuname FROM tableb p WHERE p.insurednum = '3701020000008819' GROUP BY p.insuname ) , s.maskcode = 'P30' WHERE s.pffno = '2671267' ;
I am not sure if this a bug or a feature, because the Extra-Brackets would define a ROW Object like in
UPDATE table_name SET ( a, b, c) = ( 1, 2, 3 )
Beta Was this translation helpful? Give feedback.
All reactions
-
Do you mean that this bracket causes this problem? If it is a field query, it is easy to solve, but if it is multiple fields,
for example:
UPDATE
taba s
SET
(
s.appname,
s.packagermark,
s.packagecode
) =
(SELECT
p.insuname,
p.insunvalue,
p.billntfee
FROM
tableb p
WHERE p.insurednum = '3701020000008819'
GROUP BY p.insuname),
s.maskcode = 'P30'
WHERE s.pffno = '2671267' ;
in this way, there is no way to simplify this complex SQL,Do you have any good suggestions or methods that can be corrected?
Beta Was this translation helpful? Give feedback.
All reactions
-
Greetings.
your statement as a syntax error s.maskcode = 'P30' but will work otherwise:
UPDATE taba s SET ( s.appname , s.packagermark , s.packagecode ) = ( SELECT p.insuname , p.insunvalue , p.billntfee FROM tableb p WHERE p.insurednum = '3701020000008819' GROUP BY p.insuname ) WHERE s.pffno = '2671267' ;
You can test it online here
Beta Was this translation helpful? Give feedback.
All reactions
-
I know what you mean. I use the website provided by you to verify. Indeed, this SQL will report errors, but I use it in oralcle PL / SQL developer. It is normal and can be executed and submitted:
_UPDATE
prpjpaymentbill b
SET
(
b.packagecode,
b.packageremark,
b.agentcode
) =
(SELECT
p.payrefreason,
p.classcode,
p.riskcode
FROM
prpjcommbill p where p.policertiid='SDDH200937010330006366' ),
b.payrefnotype = '05',
b.packageunit = '4101170402'
where b.payrefno='B370202091026000005' ;
_
图片
Beta Was this translation helpful? Give feedback.
All reactions
-
Details as below, all these samples work on H2 and I will look into it.
I opened issue #1316 accordingly.
CREATE TABLE test ( a VARCHAR (1) , b VARCHAR (1) ) ; -- queries are supported update test set (a, b) = (select '1', '2'); -- value lists are not supported update test set (a, b) = values( '1', '2'); -- simple expression lists are not supported update test set (a, b) = ('1', '2'); -- complex expression lists are not supported update test set (a, b) = (1, (select 2));
Beta Was this translation helpful? Give feedback.
All reactions
-
OK, I see. Thank you very much!
Beta Was this translation helpful? Give feedback.
All reactions
-
PR #1317 solves this issue. Thanks for reporting.
Beta Was this translation helpful? Give feedback.
All reactions
-
I have verified that it is indeed OK. Thank you again. Thank you very much!
Beta Was this translation helpful? Give feedback.