New to Updates - 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

New to Updates

Author

Topic

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2008-07-09 : 11:11:44

If;

select a.pname, a.pnum, b.pnum, c.D1id
from dbo.project a
inner join dbo.master b
on a.pnum = b.pnum
inner join dbo.Updates c
on b.D1id = c.D1id

Returns to me the following;

Old Name1 348 348 058839
Old Name2 349 349 063562
Old Name3 350 350 088827
Old Name4 344 344 018313
Old Name5 347 347 050216
Old Name6 351 351 099626
Old Name7 352 352 112506
Old Name8 345 345 040797
Old Name9 346 346 040797

And the contents of dbo.Updates are;

New Name1 058839
New Name2 063562
New Name3 088827
New Name4 018313
New Name5 050216
New Name6 099626
New Name7 112506
New Name8 040797
New Name9 040797

Then how come;
update dbo.project
set pname = a.Pnewname
from dbo.Updates a
inner join dbo.master b
on pnum = b.pnum
where b.D1id = a.D1id
and type = 'work'

Returns this?
New Name 348 348 058839
New Name1 349 349 063562
New Name 350 350 088827
New Name1 344 344 018313
New Name 347 347 050216
New Name1 351 351 099626
New Name 352 352 112506
New Name1 345 345 040797
New Name 346 346 040797

Am I missing some other condition in the update statement?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-07-09 : 11:14:42

i don't see you joining to dbo.project in your update.

read this to understand the methodology for this:
http://weblogs.sqlteam.com/mladenp/archive/2007/08/19/60292.aspx

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2008-07-09 : 11:19:03

But aren't I updating dbo.project? Where would the additional join appear?

quote:
Originally posted by spirit1

i don't see you joining to dbo.project in your update.

read this to understand the methodology for this:
http://weblogs.sqlteam.com/mladenp/archive/2007/08/19/60292.aspx

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-09 : 11:47:08

quote:
Originally posted by youruseridistoxic

But aren't I updating dbo.project? Where would the additional join appear?

quote:
Originally posted by spirit1

i don't see you joining to dbo.project in your update.

read this to understand the methodology for this:
http://weblogs.sqlteam.com/mladenp/archive/2007/08/19/60292.aspx

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

i think it should be this:-

update p
set p.pname = a.Pnewname
from dbo.project p
inner join dbo.master b
on p.pnum = b.pnum
inner join dbo.Updates a
on  b.D1id = a.D1id
and type = 'work'

youruseridistoxic
Yak Posting Veteran

65 Posts

Posted - 2008-07-09 : 16:54:34

Works perfect. Thank you very much.

quote:

i think it should be this:-

update p
set p.pname = a.Pnewname
from dbo.project p
inner join dbo.master b
on p.pnum = b.pnum
inner join dbo.Updates a
on  b.D1id = a.D1id
and type = 'work'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources