sql server - sql update based on column names -

sql server - sql update based on column names -

i have problem.

i have t1, t2, t_join tables.

t_join: first column: id (unique) e.g.: 10,11,12,13. sec column: code, contains attributes equals column names of t2. e.g.: type, source, section, importance. these identified id in t1. according this, id of attribute 'source' 11.

id code 10 type 11 source 12 section 13 importance

in table t1, first column data_id not unique: 1020, 1020, 1020, 1022, 1022, 1022, 1023, 1023, 1028, 1028, 1028, 1035, 1035, etc.

the sec column id t_join. @ illustration 4 id can belong 1 data_id, these declare, of value appears in 3rd 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 item id 1020 type 1, originates source no.123, real object identified id stored in section 9 , has 4th level importance.

now, have table t2. first column same data_id in t1. in table these unique. other columns: (how surprising!) type, source, section, importance. (in reality, there not 4 attributes, @ to the lowest degree fifty!) table looks 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 newer data. update t1.value column these. next examples above, updated t1 should 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, @ data_id 1020, importance 4 , turned 2 because in t1 id 13 , refers attribute 'importance' t_join table , on. update info in way. i'm not sql expert , i've managed create code:

update t1 set value = (select * t2 inner bring together t_join on id= (select c.name sys.objects o inner bring together sys.columns c on c.object_id = o.object_id , o.name = 't2') t1.data_id = t2.data_id , t2.id = t_join.id) t1 inner bring together t2 on t1.data_id = t2.data_id inner bring together t_join on t1.id = t_join.id select * t1

but doesn't work, error message:

msg 116, level 16, state 1, line 16 1 look can specified in select list when subquery not introduced exists.

i tried solve cursor statement , declared variables (based on advice) doesn't work either.

please, if has thought how solve (in simplest way), reply detailed possible.

the problem current design have 1 table normalized , 1 de-normalized , need perform update.

you first, want de-normalize t2 table, take columns , convert rows. in sql server 2005+, introduced unpivot function perform you.

the first step select info t2 , t_join rows. select statement is:

select j.id, j.code, u.data_id, u.value t_join j inner bring together ( select data_id, col, value t2 unpivot ( value col in (type, source, section, importance) ) unpiv ) u on j.code = u.col

see sql fiddle demo. takes column info , converts rows giving 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 info in format, can utilize in update statement:

update t1 set t1.value = t.value t1 inner bring together ( select j.id, j.code, u.data_id, u.value t_join j inner bring together ( select data_id, col, value t2 unpivot ( value col in (type, source, section, importance) ) unpiv ) u on j.code = u.col ) t on t1.data_id = t.data_id , t1.id = t.id;

see sql fiddle demo.

the next problem stated have there 50 columns need unpivot. if case, can utilize dynamic sql list of columns turn rows. dynamic sql script be:

declare @colsunpivot nvarchar(max), @query nvarchar(max) select @colsunpivot = stuff((select ','+quotename(c.name) sys.columns c c.object_id = object_id('t2') , c.name not in ('data_id') xml path('')), 1, 1, '') set @query = 'update t1 set t1.value = t.value t1 inner bring together ( select j.id, j.code, u.data_id, u.value t_join j inner bring together ( select data_id, col, value t2 unpivot ( value col in ('+@colsunpivot+') ) unpiv ) u on j.code = u.col ) t on t1.data_id = t.data_id , t1.id = t.id;' exec(@query);

see sql fiddle demo.

the code update t1 next 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 |

sql sql-server tsql unpivot