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

Question

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]

I don't understand the question. Can you rephrase? @mkRabbani — ire, Jun 13 '19 at 08:45
Post your both table and view structure and mention the Key column between two table. Only then people will able to help you to the point. — mkRabbani, Jun 13 '19 at 08:46

score 2 · Accepted Answer · edited Jun 23 '19 at 05:14

2

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

edited Jun 23 '19 at 05:14

marc_s

732,580
175
1,330
1,459

answered Jun 13 '19 at 08:45

M. Kanarkowski

2,155
8
14

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

1 Answers1