I have a common SQL that cannot be parsed

[jiezhuCl]

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

[manticore-projects]

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 )

[jiezhuCl]

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?

[manticore-projects]

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

[jiezhuCl]

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' ;
_

[manticore-projects]

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

[jiezhuCl]

OK, I see. Thank you very much！

[manticore-projects]

PR #1317 solves this issue. Thanks for reporting.

[jiezhuCl]

I have verified that it is indeed OK. Thank you again. Thank you very much!