How to create column values by looking at other columns in a table? SQL

1
votes

I have three columns in a table.

Requirements: The value of col2 and col3 should make col1.

Below shows the table I have right now, which needs to be change. 

col1 col2 col3 
      AB   football
      AB   football 
      ER      driving
      ER      driving
      TR      city   
      TR      city

Below shows the table that needs to be change to 

col1             col2 col3 
AB_football_1    AB   football
AB_football_2    AB   football 
ER_driving_1     ER   driving
ER_driving_2     ER   driving
TR_city_1        TR   city   
TR_city_2        TR   city

As you can see in col1, it should take col2, put (underscore), then col3, put (underscore) then increment the number according to the values in col2 and col3.

Can this be approached within CREATE or SELECT or INSERT statement or Trigger Statement, if so any tips would be grateful.

3
sqloracle
Why not generate that value at SELECT, instead of INSERT ? – Oto Shavadze
Why do you need to do this? If it's to generate a unique key, why repeat information rather than adding a column that holds a unique number for each col2 and col3, and include that in the unique key? It's easy enough to concatenate the three columns together if you need to display the key that way. – Boneist
Which version of Oracle database do you use? – Jacob
@OtoShavadze, Yeah I would do that. Any idea on how it? – John Smith
look for row_num function – maSTAShuFu

3 Answers

1
votes

Try as

 SELECT col2 
       ||'_' 
       ||col3 
       ||'_' 
       ||rank col1, 
       col2, 
       col3 
FROM   (SELECT col2, 
               col3, 
               ROW_NUMBER() 
                 OVER( 
                   PARTITION BY col2, col3 
                   ORDER BY col2) rank 
        FROM   my_table)

Output

+---------------+------+----------+
|     COL1      | COL2 |   COL3   |
+---------------+------+----------+
| AB_football_1 | AB   | football |
| AB_football_2 | AB   | football |
| ER_driving _1 | ER   | driving  |
| ER_driving _2 | ER   | driving  |
| TR_city    _1 | TR   | city     |
| TR_city    _2 | TR   | city     |
+---------------+------+----------+
0
votes
/* table is */
col1  col2  col3
      test  123

/* Try this query */

UPDATE `demo`
SET `col1` = concat(col2, '_', col3)

/* Output will be */
col1      col2  col3
test_123  test  123
0
votes

This is easy to do (at SELECT) using row_number() window function , something like this:

select  
col2 ||'_'|| col3 ||'_'|| row_number() over(partition by col2, col3 order by col2) as col1, 
col2, 
col3
from t