sql update based on column names

Question

I have a problem.

I have T1, T2, T_Join tables.

T_Join: first column: ID (unique) e.g.: 10,11,12,13. Second column: CODE, it contains attributes which are equals to the column names of T2. E.g.: type, source, section, importance. These are identified by the ID in the T1. According to this, the ID of attribute 'source' is 11.

ID  CODE
10  type
11  source
12  section
13  importance

In table T1, the first column is the data_ID which is not unique: 1020, 1020, 1020, 1022, 1022, 1022, 1023, 1023, 1028, 1028, 1028, 1035, 1035, etc.

The second column is ID from T_Join. At this example 4 ID can belong to 1 data_ID, these declare, of which value appears in the third column (VALUE):

data_ID  ID  VALUE  
1020     10  1
1020     11  123
1020     12  9
1020     13  4
1022     10  2
1022     12  15
1023     10  2
1023     11  108
1023     13  2
1028     12  20

...

It means the item with ID 1020 is type 1, originates from source No.123, the real object which identified by this ID stored in the section 9 and has a 4th level importance.

Now, I have a table T2. The first column is the same data_ID as in T1. In this table these are unique. The other columns: (how surprising!) type, source, section, importance. (In reality, there are not only four attributes, but at least fifty!) So the table looks something like this:

data_ID  type  source  section  importance
1020     1     123     9        2
1022     1     95      3        5
1023     2     108     21       4
1028     1     147     17       5

The T2 contains the newer data. I would like to update the T1.VALUE column with these. Following my examples above, The updated T1 should look like this:

data_ID  ID  VALUE  
1020     10  1
1020     11  123
1020     12  9
1020     13  2
1022     10  1
1022     12  3
1023     10  2
1023     11  108
1023     13  4
1028     12  17
...

So, at data_ID 1020, the importance was 4 and it turned to 2 because in the T1 the ID is 13 and it refers to attribute 'importance' from T_Join table and so on. I would like to update all the data in this way. I'm not an SQL expert and I've managed to create this code:

update T1 set VALUE = 
(select * from T2 
 inner join T_Join on ID=
(SELECT 
    c.name 
FROM
    sys.objects o
INNER JOIN
    sys.columns c
ON
    c.object_id = o.object_id
AND o.name = 'T2') 
where T1.data_ID = T2.data_ID and T2.ID = T_Join.ID)

from T1
inner join T2 on T1.data_ID = T2.data_ID
inner join T_Join on T1.ID = T_Join.ID

select * from T1

but it doesn't work, the error message:

Msg 116, Level 16, State 1, Line 16 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I tried to solve it with CURSOR statement and declared variables (based on an advice) but it doesn't work either.

Please, if somebody has an idea how i could solve this (in the simplest way), answer as detailed as possible.

Answer 1

The problem with your current design is you have one table that is normalized and one that is de-normalized and you need to perform an update.

You first, will want to de-normalize the T2 table, which will take the columns and convert it into rows. In SQL Server 2005+, they introduced the UNPIVOT function which will perform this for you.

The first step is to SELECT the data from T2 and T_Join into rows. The SELECT statement is:

select j.id,
  j.code,
  u.data_id,
  u.value
from T_Join j
inner join
(
  select data_id, col, value
  from T2
  unpivot
  (
    value
    for col in (type, source, section, importance)
  ) unpiv
) u
  on j.code = u.col

See SQL Fiddle with Demo. This takes your column data and converts it to rows giving the result:

| ID |       CODE | DATA_ID | VALUE |
-------------------------------------
| 10 |       type |    1020 |     1 |
| 11 |     source |    1020 |   123 |
| 12 |    section |    1020 |     9 |
| 13 | importance |    1020 |     2 |
| 10 |       type |    1022 |     1 |
| 11 |     source |    1022 |    95 |
| 12 |    section |    1022 |     3 |
| 13 | importance |    1022 |     5 |
| 10 |       type |    1023 |     2 |
| 11 |     source |    1023 |   108 |
| 12 |    section |    1023 |    21 |
| 13 | importance |    1023 |     4 |
| 10 |       type |    1028 |     1 |
| 11 |     source |    1028 |   147 |
| 12 |    section |    1028 |    17 |
| 13 | importance |    1028 |     5 |

Once the data is in that format, you can use it in an UPDATE statement:

update t1
set t1.value = t.value
from t1
inner join
(
  select j.id,
    j.code,
    u.data_id,
    u.value
  from T_Join j
  inner join
  (
    select data_id, col, value
    from T2
    unpivot
    (
      value
      for col in (type, source, section, importance)
    ) unpiv
  ) u
    on j.code = u.col
) t
  on t1.data_id = t.data_id
  and t1.id = t.id;

See SQL Fiddle with Demo.

The next problem that you stated you have is that there are about 50 columns that you need to unpivot. If that is the case, then you can use dynamic SQL to get the list of columns to turn into rows. You dynamic SQL script will be:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @colsUnpivot = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('T2') and
               C.name not in ('data_ID')
         for xml path('')), 1, 1, '')

set @query 
  = 'update t1
     set t1.value = t.value
     from t1
     inner join
     (
       select j.id,
         j.code,
         u.data_id,
         u.value
       from T_Join j
       inner join
       (
         select data_id, col, value
         from T2
         unpivot
         (
           value
           for col in ('+@colsUnpivot+')
         ) unpiv
       ) u
         on j.code = u.col
     ) t
       on t1.data_id = t.data_id
       and t1.id = t.id;'

exec(@query);

See SQL Fiddle with Demo.

The code will update T1 with the following result:

| DATA_ID | ID | VALUE |
------------------------
|    1020 | 10 |     1 |
|    1020 | 11 |   123 |
|    1020 | 12 |     9 |
|    1020 | 13 |     2 |
|    1022 | 10 |     1 |
|    1022 | 12 |     3 |
|    1023 | 10 |     2 |
|    1023 | 11 |   108 |
|    1023 | 13 |     4 |
|    1028 | 12 |    17 |