There are two tables t1 and t2(connected by language_key) out of which a new table t3 is to be built and t3 should have same columns as t1 in SQL. Example below:

-- Table t1

-- Table t2

The table t3 should have all the columns of t1 but the values inside the column Spanish should correspond to the description from t2 where code=S and Greek should correspond to the column description from t2 where code=G..

This has to be done using Select statements and not an Update statement. Here is what I tried:

CREATE TABLE t3 AS
WITH w_rr AS(
    SELECT * FROM t1,
w_RRS AS (
    SELECT * FROM  t2

SELECT t1.language_key,t1.Spanish,t1.Greek,t2.description AS Spanish
FROM w_rr t1
INNER JOIN w_RRS t2
ON t1.language_key =  t2.language_key
WHERE  t2.Code = 'S' 

I could successfully do it for Spanish column but unfortunately unable to do it along the Greek column..

Is there a better way to acheive this..Would be great help

Thank you.

CodePudding user response：

Assume the t1 and t2 have language_key column based on your SQL script:

t1:

language_key|spanish|greek|
------------ ------- ----- 
es          |row1   |row3 |
el          |row2   |row4 |

t2:

language_key|code|description|
------------ ---- ----------- 
es          |S   |row 10     |
el          |G   |row 11     |

With the following logic, spanish & greek columns will be replaced by description column in t2 if there's a match:

select t1.language_key,
       case when t2.code='S' then t2.description else t1.spanish end as spanish,
       case when t2.code='G' then t2.description else t1.greek   end as greek
  from t1
  join t2
 using (language_key);

Outcome:

language_key|spanish|greek |
------------ ------- ------ 
es          |row 10 |row3  |
el          |row2   |row 11|