Update a Table to add a new column and set values via a LEFT JOIN

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?

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.

Advertisement

Answer