query help - 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

query help

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2012-03-13 : 10:51:01

Hi All,

I have this table


Col1    Col2    date          col5         Tech
DET     12      12/23/2009     0         12345N
DET     12      12/23/2009     1           
DET     12      12/23/2009     2     

PQR     13      12/15/2009     0         6987P
PQR     13      12/15/2009     1         
PQR     13      12/15/2009     2 
PQR     13      12/15/2009     3

I want to update Tech column with the same value that it has with the first row as long as the col1 row = col2 row and dates are also equal

so I want the result like this


Col1    Col2    date          col5         Tech
DET     12      12/23/2009     0         12345N
DET     12      12/23/2009     1         12345N
DET     12      12/23/2009     2         12345N

PQR     13      12/15/2009     0         6987P
PQR     13      12/15/2009     1         6987P
PQR     13      12/15/2009     2         6987P
PQR     13      12/15/2009     3         6987P

i tried to do this with self join, but it didn't work. below is what i tried to do

Update a
set tech= B.tech
from table1 a INNEr JOIN table1 b
ON
a.col1 =b.col1
and a.col2 =b.col2
and a.date =b.date

The aboe query didn't work.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-13 : 10:59:13

quote:
Originally posted by anjali5

Hi All,

I have this table

Col1 Col2 date col5 Tech
DET 12 12/23/2009 0 12345N
DET 12 12/23/2009 1
DET 12 12/23/2009 2

PQR 13 12/15/2009 0 6987P
PQR 13 12/15/2009 1
PQR 13 12/15/2009 2
PQR 13 12/15/2009 3

I want to update Tech column with the same value that it has with the first row as long as the col1 row = col2 row and dates are also equal

so I want the result like this

Col1 Col2 date col5 Tech
DET 12 12/23/2009 0 12345N
DET 12 12/23/2009 1 12345N
DET 12 12/23/2009 2 12345N

PQR 13 12/15/2009 0 6987P
PQR 13 12/15/2009 1 6987P
PQR 13 12/15/2009 2 6987P
PQR 13 12/15/2009 3 6987P

i tried to do this with self join, but it didn't work. below is what i tried to do

Update a
set tech= B.tech
from table1 a INNEr JOIN table1 b
ON
a.col1 =b.col1
and a.col2 =b.col2
and a.date =b.date
and isnull(b.tech,'') <> ''

The aboe query didn't work.

No, you're never too old to Yak'n'Roll if you're too young to die.

anjali5
Posting Yak Master

121 Posts

Posted - 2012-03-13 : 11:39:02

Thank you sir!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources