Updating sql based on column names

Question

I have a problem.

I have tables T1 , T2 , T_Join .

T_Join : first column: ID (unique), for example: 10,11,12,13. Second column: CODE , it contains attributes equal to T2 column names. For example: type, source, section, importance. They are identified by ID in T1 . Accordingly, the identifier of the source attribute is 11.

 ID CODE 10 type 11 source 12 section 13 importance 

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

The second column is the ID from T_Join . In this example, 4 IDs can belong to 1 data_ID , they declare, from which the 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 

...

This means that the element with identifier 1020 is type 1, comes from the source No.123, a real object, which is identified by this identifier, stored in section 9, and has a value of level 4.

Now I have a table T2. The first column is the same data identifier as in T1. In this table they are unique. Other columns: (how amazing!) Type, source, section, importance. (In fact, there are not only four attributes, but not less than 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 

T2 contains new data. I would like to update the T1.VALUE column. Following the 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, in data_ID 1020, the value was 4, and it turned out to be 2, because in T1 the identifier is 13, and it refers to the value of the attribute from the T_Join table, etc. I would like to update all the data in this way. I am not an SQL expert, and I 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 this does not work, error message:

Msg 116, Level 16, State 1, Line 16 Only one expression can be specified in the selection list if a subquery is not entered with EXISTS.

I tried to solve it using the CURSOR operator and declared variables (based on advice), but it does not work either.

Please, if someone has an idea how I can solve this (in the easiest way), please answer as detailed as possible.

Answer 1

The problem with your current design is that you have one normalized table and the other is unnormalized, and you need to perform an update.

First, you want to de-normalize the T2 table, which will take the columns and convert them to rows. In SQL Server 2005+, they introduced a UNPIVOT function that will do this for you.

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

 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 into 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 this format, you can use it in the UPDATE :

 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 you have reported is that there are about 50 columns that you need to disable. If so, then you can use dynamic SQL to get a list of columns to convert to rows. 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 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 |