Subquery in update statement

[leonylcatwork]

The language of WCDB

e.g. Objective-C, Swift or Java

Objective-C

The version of WCDB

e.g. v1.0.5

1.0.7.5

The platform of WCDB

e.g. iOS, macOS or Android

iOS

The installation of WCDB

e.g. Cocoapods, Carthage, Maven, AAR Package or Git clone

Cocoapods

What's the issue?

SQL in question is:

UPDATE table1 SET column1=(SELECT column2 FROM table2 WHERE column3=column1)) WHERE column1 <= 0

WCDB code:

StatementSelect subquery = StatementSelect()
        .select({ColumnResult(Table2.column2)})
        .from(Table2.name.UTF8String)
        .where(Table2.column3 == Table1.column1);

UpdateValueList setClause = {UpdateValue(Table1.column1, Expr(subquery)};

StatementUpdate update = StatementUpdate()
        .update(Table1.name.UTF8String)
        .set(setClause)
        .where(Table1.column1 <= 0);

Error:

near "SELECT": syntax error

If we print update.getDescription(), we get

UPDATE table1 SET column1=SELECT column2 FROM table2 WHERE column3=column1 WHERE column1 <= 0

Note that the brackets surrounding SELECT subquery are missing.

Related issue: #337

My solution is to add an method in expr.hpp:

Expr subquery() const;

and in expr.cpp:

Expr Expr::subquery() const
{
    Expr expr;
    expr.m_description.append("(" + m_description + ")");
    return expr;
}

So the setClause can be:

UpdateValueList setClause = {UpdateValue(Table1.column1, Expr(subquery).subquery()};

[RingoD]

Try pre_v1.1.0 branch.

[Qiuwen-chen]

Please try the latest version.