Join a column with two tables

Question

Is it possible to update table column data by combining column data from another table? Let me explain with a simple example,

Table A: studentaccess

╔════════╦══════════╗
║ UserID ║ AccCode  ║
╠════════╬══════════╣
║    12  ║ Tom      ║
║    13  ║ Ann      ║
╚════════╩══════════╝

Table B: studentdetails

╔════════╦═══════════════════╗
║ ID     ║ UserName          ║
╠════════╬═══════════════════╣
║    1   ║ raj_12_kumar      ║
║    2   ║ test_13_test      ║
╚════════╩═══════════════════╝

Now I want to split the column data UserNameand extract the second token from this and query the table studentaccess, matching UserIDand get the value AccCodefrom it and combine it with the second token of the column data UserName. Finally, I need the table data studentdetailsas shown below,

╔════════╦═══════════════════╗
║ ID     ║ UserName          ║
╠════════╬═══════════════════╣
║    1   ║ 12_Tom            ║
║    2   ║ 13_Ann            ║
╚════════╩═══════════════════╝

From the substr query below, I can get the value UserIDfrom a column UserNamein a table studentdetails,

select regexp_substr(UserName, '([^_]+)(_[^_]+){1}$', 1, 1, null, 1) as userId  from studentdetails

Does anyone help me combine the same with AccCodeand update it in a column UserName?

Answer 1

, .

Oracle sqlfiddle: http://sqlfiddle.com/#!4/cd66c/1

Oracle

UPDATE studentdetails sd
SET sd.UserName =
  (SELECT  to_char(x.UserID) || '_' || sa.AccCode
   FROM 
     (SELECT  sd1.ID as ID, SUBSTR(sd1.UserName, INSTR( sd1.UserName,'_') + 1, INSTR( sd1.UserName,'_', INSTR( sd1.UserName,'_') + 1) - 1 - INSTR( sd1.UserName,'_')) AS UserID
      FROM studentdetails sd1 
     ) x  INNER JOIN studentaccess sa ON TO_NUMBER(x.UserID) = sa.UserID where sd.ID = x.ID   
  );

SQL Server

UPDATE studentdetails
SET UserName =
  (SELECT x.UserID + '_' + sa.AccCode
   FROM
     (SELECT SUBSTRING(sd1.UserName, CHARINDEX('_', sd1.UserName) + 1, CHARINDEX('_', sd1.UserName, CHARINDEX('_', sd1.UserName) + 1) - 1 - CHARINDEX('_', sd1.UserName)) AS UserID
      FROM studentdetails sd1
      WHERE sd1.ID = studentdetails.ID) x
   INNER JOIN studentaccess sa ON x.UserID = sa.UserID);

1

, , UNKNOWN UserName studentdetails, studentaccess , studentdetails.

sqlfiddle, sqlfiddle - . , , . - .

UPDATE studentdetails sd
SET sd.UserName =
  (SELECT  CASE when x.ID is null then 'UNKNOWN' else  to_char(x.UserID) || '_' || sa.AccCode END
   FROM 
     (SELECT  sd1.ID as ID, SUBSTR(sd1.UserName, INSTR( sd1.UserName,'_') + 1, INSTR( sd1.UserName,'_', INSTR( sd1.UserName,'_') + 1) - 1 - INSTR( sd1.UserName,'_')) AS UserID
      FROM studentdetails sd1 
     ) x  RRIGHT OUTER JOIN studentaccess sa ON TO_NUMBER(x.UserID) = sa.UserID where sd.ID = x.ID   
  );

Answer 2

UPDATE StudentDetails sd
SET UserName = 
( 
    SELECT userid||'_'||AccCode 
    FROM StudentAccess sa 
    WHERE regexp_substr(sd.UserName, '([^_]+)(_[^_]+){1}$', 1, 1, null, 1) = sa.userid
);

Answer 3

You are almost close. You can associate another column with the username.

regexp_substr(UserName, '([^_]+)(_[^_]+){1}$', 1, 1, null, 1) || '_' || acccode

The result with your sample data will be like this:

    select userid, regexp_substr(UserName, '([^_]+)(_[^_]+){1}$', 1, 1, null, 1) || '_' || acccode as username  from 
(
select 1 UserID, 'Tom' AccCode from dual
union
select  1 userid , 'Ann' AccCode from dual) studentaccess,
( select 1 ID, 'raj_12_kumar' username from dual
 union
 select 2 id, 'test_13_test' username from dual) studentdetails
 where studentaccess.userid = studentdetails.ID