Can SELECT from table but not UPDATE it (get "relation [table name] does not exist" when attempting to UPDATE

Question

When I tried to update several columns in a table using a batch execute in Dbeaver, the update statement worked for the first column (the first execute), but then all subsequent executes for the rest of the columns threw a "relation nat_res_minerals_mrds_v2 does not exist" error.

However, when I execute a select query using the same table name no error occurs and I see my data just fine.

I checked for any constraints, found two and removed one of them--however one of the constraints begins with a number, so when I try to drop it (even when surrounding the column name with double quotes "") I get a "constraint does not exist" error.

So I tried to work around this by selecting the data into a new table, checked if any constraints carried over (they didn't), but the problem still persists.

Also, interestingly despite the fact that the first update statement worked, subsequent attempts to execute the same update statement on the same column now throw the "relation nat_res_minerals_mrds_v2 does not exist" error.

I'm not really sure how to proceed--especially since the first update statement worked and selecting from the table always worked and continues to work.

Any input would be appreciated.

EDIT:

here are the actual queries:

update nat_res_minerals_mrds_v2 n set mdscr1 = x.code_dscr from xwalk_nat_resources_minerals_codelist x where x.code = n.m1 and n.m1<>'';
update nat_res_minerals_mrds_v2 n set mdscr2 = x.code_dscr from xwalk_nat_resources_minerals_codelist x where x.code = n.m2 and n.m2<>'';
update nat_res_minerals_mrds_v2 n set mdscr3 = x.code_dscr from xwalk_nat_resources_minerals_codelist x where x.code = n.m3 and n.m3<>'';
update nat_res_minerals_mrds_v2 n set mdscr4 = x.code_dscr from xwalk_nat_resources_minerals_codelist x where x.code = n.m4 and n.m4<>'';
update nat_res_minerals_mrds_v2 n set mdscr5 = x.code_dscr from xwalk_nat_resources_minerals_codelist x where x.code = n.m5 and n.m5<>'';
update nat_res_minerals_mrds_v2 n set mdscr6 = x.code_dscr from xwalk_nat_resources_minerals_codelist x where x.code = n.m6 and n.m6<>'';
update nat_res_minerals_mrds_v2 n set mdscr7 = x.code_dscr from xwalk_nat_resources_minerals_codelist x where x.code = n.m7 and n.m7<>'';
update nat_res_minerals_mrds_v2 n set mdscr8 = x.code_dscr from xwalk_nat_resources_minerals_codelist x where x.code = n.m8 and n.m8<>'';
update nat_res_minerals_mrds_v2 n set mdscr9 = x.code_dscr from xwalk_nat_resources_minerals_codelist x where x.code = n.m9 and n.m9<>'';
update nat_res_minerals_mrds_v2 n set mdscr10 = x.code_dscr from xwalk_nat_resources_minerals_codelist x where x.code = n.m10 and n.m10<>'';
update nat_res_minerals_mrds_v2 n set mdscr11 = x.code_dscr from xwalk_nat_resources_minerals_codelist x where x.code = n.m11 and n.m11<>'';
update nat_res_minerals_mrds_v2 n set mdscr12 = x.code_dscr from xwalk_nat_resources_minerals_codelist x where x.code = n.m12 and n.m12<>'';
update nat_res_minerals_mrds_v2 n set mdscr13 = x.code_dscr from xwalk_nat_resources_minerals_codelist x where x.code = n.m13 and n.m13<>'';
update nat_res_minerals_mrds_v2 n set mdscr14 = x.code_dscr from xwalk_nat_resources_minerals_codelist x where x.code = n.m14 and n.m14<>'';
update nat_res_minerals_mrds_v2 n set mdscr15 = x.code_dscr from xwalk_nat_resources_minerals_codelist x where x.code = n.m15 and n.m15<>'';
update nat_res_minerals_mrds_v2 n set mdscr16 = x.code_dscr from xwalk_nat_resources_minerals_codelist x where x.code = n.m16 and n.m16<>'';
update nat_res_minerals_mrds_v2 n set mdscr17 = x.code_dscr from xwalk_nat_resources_minerals_codelist x where x.code = n.m17 and n.m17<>'';
update nat_res_minerals_mrds_v2 n set mdscr18 = x.code_dscr from xwalk_nat_resources_minerals_codelist x where x.code = n.m18 and n.m18<>'';
select *
    from nat_res_minerals_mrds_v2;

I'm not too familiar with dBeaver but I believe you need a commit or go statement after each update statement to update the table. — Jim L
No, you don't. I've done exactly this kind of thing before countless times for quite a while. Only when I'm connecting to my DB through python etc. do I need to execute a a commit [i.e. cursor.execute(..."] — gulfy
It sounds like you have lots of experience with this. I can only recommend that you look at your relationships between the tables/fields. Maybe you are updating a column that has a restriction based on a relation constraint. Is the table actually a view? Could it be permissions issue? — Jim L

Jim L Jim L · Accepted Answer · 2020-02-08T15:28:22

You were missing the select statement.

update nat_res_minerals_mrds_v2 n 
set mdscr1 = 
(
select x.code_dscr 
from xwalk_nat_resources_minerals_codelist x 
where x.code = n.m1
)
where n.m1<>'';

Can SELECT from table but not UPDATE it (get "relation [table name] does not exist" when attempting to UPDATE

2 Answers