update.mdx

title	UPDATE FROM SELECT Statement
sidebarTitle	UPDATE

Description

The UPDATE FROM SELECT statement updates data in existing table. The data comes from a subselect query. It can be used as alternative to 'create table' and 'insert into' for store predictions in distinct columns of existing rows

Syntax

Here is an example:

UPDATE
    int2.table2
SET
    predicted = source.result,
FROM
 (
     SELECT p.result, p.prod_id, p.shop_id
      FROM int1.table1 as t
     JOIN mindsdb.pred1 as p
 ) AS source
WHERE
    prod_id = source.prod_id
    and shop_id = source.shop_id

And the steps followed by the syntax:

• It executes query from 'FROM' block to get the output data. In our example it is join of table table1 (from integration int1) with predictor pred1. It also can be select from integration
• source is the alias for fetched data
• then it updates table2 from int2 using conditions from WHERE block and fields for update from SET block
  • under the hood it splits input data to rows and execute this query for every row:

  UPDATE
      table2
  SET
      predicted = <row.result>,
  WHERE
      prod_id = <row.prod_id>
      and shop_id = <row.shop_id>

It is recommended to use the primary key column(s) in the WHERE clause, as the primary key column(s) uniquely identify each row. Otherwise, the UPDATE statement may lead to unexpected results by altering rows that you didn't want to affect.