Updating A Column From Another Table And Concatenating Two Columns In Single Query

Question

Oracle DB In SQL:

How to Update one column of a table from a column of another table and how to concatenate two columns of first table in a single query

If column_1 of table_1 matches column_A of table_2, then update column_2 of table_1 from the values of column_B of table_2 and also concatenate column_3 and column_4 of table_1 into column_5 of table_1.

I tried :

UPDATE T1
  SET column_2 = T2.column_B,
      column5 = T1.column_3 + T1.column_4
FROM table_1 AS T1
JOIN table_2 AS T2
  ON T2.column_A = T1.column_1

I am not getting it

Whats the "+'' for? Are you concatenating with +, does it work? — Uppi
If you are looking to concatenate, you should use the method that works for the database engine you are using. Oracle has documentation on the topic. — Dan Bracuk
if you have t1.col3 and t1.col4 why bother concatenating them? seems redundant to me — Paul Maxwell

Sachu Sachu · Accepted Answer · 2016-02-14T07:19:39

You are using '+' as string concatenation. Oracle the string concatenation operator is '||' try the below code

UPDATE (SELECT t1.column_2 column2, 
               t1.column_3 column3,
               t1.column_4 column4,
               t1.column_5 column5,
               t2.column_B columnb
          FROM table_1 t1,
               table_2 t2
         WHERE t1.column_1 = t2.column_A)
   SET column2 = columnb,
       column5 = column3 || column4

Updating A Column From Another Table And Concatenating Two Columns In Single Query

1 Answers