SQL UPDATE-with-Join in MonetDB

Question

I'm using MonetDB 11.33.3 (April 2019 release). I want to update records in some table, using corresponding values from another table.

Specifically, and for example, suppose table1 has columns col1, col3 and table2 has columns col2, col4. Assume that there's an injective map from the values of table1.col3 to table2.col4 (so that when we equi-join with these columns we get at most one match). I want to set table1.col1 to the corresponding table2.col3 value - from the single matching record in table2 (using the equi-join I just mentioned).

Here is my attempted query:

WITH t2 AS (SELECT * FROM table2)
UPDATE 
table1
SET
col1 = t2.col2
WHERE
col3 = t2.col4
;

this gives me:

SELECT: identifier 'col1' unknown

I don't understand why I'm getting this particular error, and thus don't understand what I've gotten wrong or how to fix the query.

11.33.3 is very old. A lot of things have been changed, fixed and optimised. Please consider upgrading to the latest release 11.39.11. — Jennie, Feb 08 '21 at 09:36

score 0 · Answer 1 · answered Feb 06 '21 at 22:45

0

Try using a correlated subquery:

UPDATE table1
    SET col1 = (SELECT t2.col2 FROM table2 t2 WHERE table1.col3 = t2.col4)
     WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE table1.col3 = t2.col4);

answered Feb 06 '21 at 22:45

Gordon Linoff

1,242,037
58
646
786

So, there's no use for the `WITH` clause then? Also, if I need to set more fields, I'll be replicating the inner query many many times. – einpoklum Feb 06 '21 at 22:51
@einpoklum . . . You can use with to define `table2`, but you still need to reference the CTE in a `FROM` clause. – Gordon Linoff Feb 07 '21 at 01:19

score 0 · Answer 2 · answered Feb 08 '21 at 08:11

0

This looks like a bug that has been fixed. I tried on Nov2019 release, and I didn't get the error.

answered Feb 08 '21 at 08:11

p.debugging

41
1

SQL UPDATE-with-Join in MonetDB

2 Answers2