Join, Inner, Left - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join, Inner, Left

Author

Topic

jfm
Posting Yak Master

145 Posts

Posted - 2013-07-31 : 09:09:28

Hi there,

I have the following query:

use db
select a.col1,a.col2,a.col3,a.col4,a.col5,a.col6,a.col7,a.col8, b.col1 as xx, b.col2
into new_table
from t1 as a
LEFT JOIN t2 as b
on a.id = b.id
group by a.col1,a.col2,a.col3,a.col4,a.col5,a.col6,a.col7,a.col8, b.col2

I just need to update t1 with the two columns of t2 when the id's are matching. Why is not working this query? Any tip?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-31 : 09:14:23

quote:
Originally posted by jfm

Hi there,

I have the following query:

use db
select a.col1,a.col2,a.col3,a.col4,a.col5,a.col6,a.col7,a.col8, b.col1 as xx, b.col2
into new_table
from t1 as a
LEFT JOIN t2 as b
on a.id = b.id
group by a.col1,a.col2,a.col3,a.col4,a.col5,a.col6,a.col7,a.col8, b.col2

I just need to update t1 with the two columns of t2 when the id's are matching. Why is not working this query? Any tip?

Not sure what you meant by "not working".

One thing that I do see is that you have two columns with the same name (a.col2 and b.col2) in the select list. That is not allowed when you have the select .. into construct. So you will need to alias one or the other.

Second, you said you wanted to update t1. The syntax for updating is not this at all. Can you show what you want to accomplish using some sample input data and desired output data?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-07-31 : 09:18:55

There is no update in the posted query so why you are talking about update?
Not working says nothing to us! Any error messages?

b.col1 will be a problem in the select list because it isn't wrapped in an aggregate function nor it is in the group by clause.

Please be kind and help us to help you

Too old to Rock'n'Roll too young to die.

jfm
Posting Yak Master

145 Posts

Posted - 2013-07-31 : 09:31:06

Sorry this is the correction:

use db
select a.col1,a.col2,a.col3,a.col4,a.col5,a.col6,a.col7,a.col8, b.col1 as xx, b.col15
into new_table
from t1 as a
LEFT JOIN t2 as b
on a.col_id = b.col_id
group by a.col1,a.col2,a.col3,a.col4,a.col5,a.col6,a.col7,a.col8,b.col1, b.col15

What I need is take col1 and col15 from t2 (which has just col_id in common) into t1. The matter with this query is that im increasing my t1 number of rows. I guess its because im adding rows from t2 as well. Instead, I just need to add col1 and col15 of t2, in the matching id's from t1

Thank you

How can I get just the

quote:
Originally posted by webfred

There is no update in the posted query so why you are talking about update?
Not working says nothing to us! Any error messages?

b.col1 will be a problem in the select list because it isn't wrapped in an aggregate function nor it is in the group by clause.

Please be kind and help us to help you

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources