I have a table R which in part has a column C. I have another table T which also has a column C that is of the same type and points to the same variable.
I want to perform a LEFT JOIN between R and T on C so that I return the full contents of R back, but with a column D from T, i.e.
SELECT R.*, T.D FROM R LEFT JOIN T ON R.C = T.C
Note – The LEFT JOIN here is used as I only want to match those rows of T to R that exists in T and when it doesn’t exist in R, I want a NULL value.
Now, I want the returned Query to be the new definition of R. i.e.
Note – the following is pseudocode.
R = ( SELECT R.*, T.D FROM R LEFT JOIN T ON R.C = T.C )
The method I tried was the following:
CREATE TABLE "R" AS SELECT R.*, T.D FROM R LEFT JOIN T ON R.C = T.C
This produces an error. It seems that SQL (here I’m using SQLite) does not allow for this sort of reallocation.
I’m unsure how to proceed – does anyone know of a way to achieve this?
Advertisement
Answer
You can add a new column and assign it a value:
alter table r add column d <type>;
Then you can populate it:
update r set d = (select t.d from t where t.c = r.c);
However, a view seems more useful.