MindsDB provides two ways of using the UPDATE
statement:
The regular UPDATE
statement updates specific column values in an existing table.
The UPDATE FROM SELECT
statement updates data in an existing table from a subselect query. It can be used as an alternative to CREATE TABLE
or INSERT INTO
to store predictions.
Here is an example of the regular UPDATE
statement:
Please replace the placeholders as follows:
integration_name
is the name of the connected data source.table_name
is the table name within that data source.column_name
is the column name within that table.And here is an example of the UPDATE FROM SELECT
statement that updates a table with predictions made within MindsDB:
Below is an alternative for the UPDATE FROM SELECT
statement that updates a table with predictions. This syntax is easier to write.
The steps followed by the syntax:
FROM
clause to get the output data. In our example, we query for predictions, but it could be a simple select from another table. Please note that it is aliased as prediction_data
.table_to_be_updated
table (that belongs to the integration_to_be_updated
integration) that match the WHERE
clause criteria. The rows are updated with values as defined in the SET
clause.It is recommended to use the primary key column(s) in the WHERE clause (here, column1
and column2
), 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.