Need help wit a query - 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)

Need help wit a query

Author

Topic

gv_pradeep
Starting Member

19 Posts

Posted - 2008-08-16 : 03:33:41

Hi all,
I'm stuck on how to achieve the following functionality. I'll try to explain it clearly. Consider two tables.

Table1

nRollNo---------tUserId
--1---------------a_b--
--4---------------c_d--

Table2

nRollNo---------tUserId----------tPassword
--1---------------a_b--------------xyz----
--2---------------a_b--------------fgh----
--3---------------c_d--------------qwe----
--4---------------c_d--------------asd----
--5---------------e_f--------------poi----

Table2 is to be updated based on Table1. Consider nRollNo value 1 from Table1. Corresponding tUserId is a_b. So tPassword corresponding to tUserId a_b should be updated in Table2. The tPassword value to be updated corresponds to nRollNo 1 i.e xyz. Posting the expected table below.

Table2

nRollNo---------tUserId----------tPassword
--1---------------a_b--------------xyz----
--2---------------a_b--------------xyz----
--3---------------c_d--------------asd----
--4---------------c_d--------------asd----
--5---------------e_f--------------poi----

The underlined values are the updated values. Hope it's clear.

I'm trying to achieve this without using Cursors. Please let me know how it is possible.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-16 : 04:09:49

UPDATE t2
SET t2.tPassword = coalesce(h.tpassword, t2.tpassword)
FROM Table2 AS t2
LEFT JOIN (select t1.tuserid, x.tpassword from table1 as t1 inner join table2 as x on x.nrollno = t1.nrollno and x.tuserid = t1.tuserid
) as h on h.tuserid = t2.tuserid

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources