February 11, 2013 at 4:59 am
Please help!
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 were 4 and it turned 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.
Please, if somebody has an idea how i could solve this, answer as detailed as possible.
Thanks a lot!
February 11, 2013 at 5:34 am
Welcome to the forum.
I would strongly suggest you to read the tips from the link at the bottom of my signature.
Following the above, will help you to post question in a such way that it will attract more relevant and timely responses. Most of people here do expect DDL, sample data insert script and sample of wanted output together with the question description, so they do not need to spend their valuable time in order to setup everything themselves...
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply