January 24, 2011 at 11:36 am
i have a 2 tables and i want the data from one table which has multiple data for a data to be updated
to another table;
table 1:
col1 col2
a1
a2
b3
b4
c7
c10
table 2:
col1 col2
1a
11b
33c
44d
12e
17f
i want to update table 2 value by values of table1 on values of a .
as in table 1 a b c has multiple values so i want an update query that can update
table 2 with all the values of a i.e. a =1 and 2 both to be updated in table 2 on a, b ,c .
i wrote a query :
update table2
set col1=a.col2
from table1 where table1.col1=table2.col2
but the above query is only updating 1st value from table 1 but i want all the values of col1 from table 1 to table2.
January 24, 2011 at 4:04 pm
Your sample data and your expected output don't match.
Please try to describe more detailed what you're trying to achieve.
One way of doing it would be to create some sample data and present your expected output based on those data.
January 26, 2011 at 5:50 am
I ll Brief this :
I have one table eg table1 with has an id and multiple values for same id . i have one more table with the id of table1 . id of table one is not unique column . now i want to update the values of table2 with the values of id of table1.
I tried it by update query but the update is taking the first value from the multiple values of id from table1.
I want all the values for the id of table1 to be updated in table2 for the id.
Eg. i have a database of school and now i have table of students which has studentid and subjects as columns. Another table with subjectmarks with columns studid,subjects and marks as columns.
now in table one i have stdid with subjects for each id . i.e. each id will be more then once since each id have more then 2 subjects.
Now i want these all the subjects for an id to be updated in the table2 for same id which is already there.
how can i do this by update statement..I know that update can update only one row at a time .
when i m using update it is taking the first values i.e . only one subject(mostly first subject) for an id and updating in the table2.
phhhhhhhhhh i am tired... Hope now its clear wt i want.
January 26, 2011 at 9:16 am
your update query is written wrong. It should be:
update T2 set col1 = T1.col2
from table2 T2
JOIN table1 T1 on T1.col1 = T2.col2
also, as others have stated, if your real data looks like your sample data you don't have a 1-1 match from T1.col1 to T2.col2 so results will not be what you may be expecting.
The probability of survival is inversely proportional to the angle of arrival.
January 26, 2011 at 9:18 am
Please read and follow the advice given in the first link referenced in my signature.
Like I said in my previous post: the data you provided earlier don't match.
Example:
How do you end up with col1=33 for col2=c in table2 if table1 holds 7 and 10 in col2 for col1=c??
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply