How to update from one table with criteria from other table?

[edulid]

Hi,

I want to update an entry on table A based on criteria from table B. Both tables are joint by a foreign key. I want to do this from the server.

What is the best way to do this?

My tables definition:

create table myTable_A
(f_id int primary key,
f_lastname varchar(50) null,
f_firstname varchar(50) null
);

insert into myTable_A (f_id, f_lastname, f_firstname) values (1, 'A', 'X');
insert into myTable_A (f_id, f_lastname, f_firstname) values (2, 'B', 'Y');
insert into myTable_A (f_id, f_lastname, f_firstname) values (3, 'C', 'Z');

create table myTable_B
(f_id int primary key,
f_A_key int not null,
f_title varchar(50) not null
);
alter table myTable_B add constraint FK_A foreign key(f_A_key) references myTable_A(f_id);

insert into myTable_B (f_id, f_A_key, f_title) values (1, 1, 'Engineer');

myTable_A.ds.xml:

<DataSource ID="myTable_A" serverType="sql" tableName="myTable_A">
    <fields>
        <field name="f_id" type="integer" primaryKey="true" />
        <field name="f_lastname" type="text" />
        <field name="f_firstname" type="text" />

        <field name="f_title" type="text" customSelectExpression="B.f_title" />
    </fields>

    <operationBindings>
        <operationBinding operationType="fetch">
            <tableClause><![CDATA[
                myTable_A 
                left join myTable_B B on B.f_A_key = myTable_A.f_id
            ]]></tableClause>
        </operationBinding>
    </operationBindings>
</DataSource>

but I get: an erroneous SQL:

UPDATE myTable_A SET f_firstname=NULL WHERE (B.f_title = ''Engineer'' AND B.f_title IS NOT NULL) select SCOPE_IDENTITY() AS GENERATED_KEYS

I tried two things:
1. If I add an operationBinding:

..
        <operationBinding operationType="update">
            <tableClause><![CDATA[
                myTable_A 
                left join myTable_B B on B.f_A_key = myTable_A.f_id
            ]]></tableClause>
        </operationBinding>
..

I get:

UPDATE 
myTable_A 
left join myTable_B B on B.f_A_key = myTable_A.f_id
SET f_firstname=NULL WHERE (B.f_title = ''Engineer'' AND B.f_title IS NOT NULL) select SCOPE_IDENTITY() AS GENERATED_KEYS

2. I tried with customUpdateExpression, but this does not work. customUpdateExpression="B.f_title" .

What am I doing wrong? I bet there is an easy way to do this.

Using smartgwt 6.1p power 2018-03-22

[Isomorphic]

Our code for generating UPDATE SQL doesn't have any handling for joins, so you should just use <customSQL> rather than trying clause-by-clause substitutions. However, you may be able to have the WHERE clause generated for you via using SQLDataSource.getSQLClause() and then putting the generated SQL into the template context (via dsRequest.addToTemplateContext()).