UPDATE from joined table

CrateDB SQL
1

I’m curious if there isn’t any way to UPDATE a table field by getting the value from a joined table…

Neither

UPDATE table1 as t1
set xyz = (select xyz from table2 where ref_t1 = t1.id limit 1);

nor

UPDATE table1 as t1 JOIN table2 as t2 on t2.ref_t1 = t1.id
set xyz = t2.xyz;

do work. I assume that I have to solve this via any external (scripted) approach, is that correct?

1 Like
2

Hi @Jurgen_Zornig,

I don’t think this is possible yet. Might I suggest, that you open a feature request at https://github.com/crate/crate/issues

Best regards
Georg

3

@Jurgen_Zornig in this case you may want to do something like this:

CREATE TABLE table1 (id INT PRIMARY KEY,xyz TEXT);
INSERT INTO table1 VALUES (1,'old value');

CREATE TABLE table2 (ref_t1 INT,xyz TEXT,version INT);
INSERT INTO table2 VALUES 
	(1,'new value',1),
	(1,'even newer value',2);

INSERT INTO table1(id,xyz)
SELECT ref_t1,max_by(xyz,version)
FROM table2
GROUP BY ref_t1
ON CONFLICT (id) DO UPDATE SET xyz=excluded.xyz;
1 Like