INNER JOIN - 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

SQL Server 2000 Forums

SQL Server Development (2000)

INNER JOIN

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2005-10-27 : 11:13:24

Guys

I have following subset of data

file# role key grpid
_____________________________________
00347 1 19830210080034702 12092
00347 2 19830210080034700 12092
00347 1 19830210080034701 3632
00347 2 19830210080034700 3632

I am trying to update the key of same grpid with role 2 to be equal to the key of role 1.

So for grpid = 12092 and role = 2 I should have key = 19830210080034702

For this I doing the inner join on the table itself but it doesnt seem to work

UPDATE nfs
SET key = d.key
FROM nfs INNER JOIN nfs d ON nfs.grpid = d.igrpid
where nfs.role = 2

But the above update statement doesnt seem to work, do you guys have any better ideas to make this work???

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2005-10-27 : 11:47:35

UPDATE nfs
SET key = d.key
FROM nfs
INNER JOIN nfs d
ON nfs.grpid = d.igrpid
where nfs.role = 2
and d.role = 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2005-10-27 : 11:54:19

Scelamko,

Your answer is in your statement:

I am trying to update the key of same grpid with role 2 to be equal to the key of role 1.

Review your query, but translated to english:

update the key in table nfs when it has a role of 2 and it matches a record from the same table that shares a groupid with it (including itself, i.e. all records).

Where are you restricting the second rowset to be only rows with a role of 1?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources