[SOLVED] Updating table from a view, error: table must be contained in source tables, but was not found

Updating table from a view, error: table must be contained in source tables, but was not found

I'm trying to update a table from a view in Exaplus (which uses the Lua scripting language) but I keep getting the same error.

A simple update to the schema_1 table works but when I try updating a table with a view from schema_2 I always get the same error no matter how much I simplify the code.

The parameters in both the table and the view are the same datatype (varchar(3)). I have also confirmed that it is not a permission issue.

This is the code I'm running.

UPDATE schema_1.table1
SET table1_parameter = a.view_parameter
FROM schema_2.view_name AS a
WHERE table1_parameter_2 IS NOT NULL

The expected result is the table being updated, however upon running this code I get the following error:

[42000] UPDATE-target-table must be contained in source tables, but was not found [line 4, column 1]

Solution

Table1 must be a part of your statement to work. You need to join these two tables in other case sql don't know which row from your view should align with which row from table1.

UPDATE a1
SET table1_parameter = a.view_parameter
FROM schema_1.table1 AS a1
JOIN schema_2.view_name AS a
    on a1.keyColumn = a.keyColumn
WHERE table1_parameter_2 IS NOT NULL