Updating a field based on the value selected

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 2005 Forums

Transact-SQL (2005)

Updating a field based on the value selected

Author

Topic

sudha12345
Starting Member

47 Posts

Posted - 2009-08-27 : 04:33:56

I encountered a problem in Updating a Field

I has Two Tables as Below

Table 1 Table 2

ID Name ID NAME
1 System1 3 NULL
2 System2
3 NULL
4 System4

I has to Update the Column Name with System2 based on the value of I in Table 2 (Has to select the next value from Table 1 based on the Value in ID)
If the value ID in Table2 in 3 it has to select the value of ID 2 and update the Column Name

why we has to go this Approach, beacuse the Value of IDs which has Name NULL will be Placed in Table 2. There will be so many IDs . As a reference i gave one Example.

How it can be done

Sudhakar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-27 : 13:20:18

[code]UPDATE t2
SET t2.Name=t1.Name
FROM Table2 t2
CROSS APPLY(SELECT TOP 1 Name
FROM Table1
WHERE ID <= t2.ID
ORDER BY ID DESC)t1
[/code]

sudha12345
Starting Member

47 Posts

Posted - 2009-08-28 : 01:35:47

It is working fine. But it is updating the name field in Table 2. But it has to update the name field in Table 1

The func is

While the ID 3 has name NULL in Table 2, it has to take a ID from Table 1 and has to update its anme in Table 1.

(i.e The ID 3 has Name NULL, so it will be placed in Table 2, now it has to take a ID which is greater than 3 (Nearest to 3), and then it has to update the name filed in Table 1)

The Name (ID) has to selected based on the value in Table 2

It is working fine. But it is updating the name field in Table 2. But it has to update the name field in Table 1

The func is

While the ID 3 has name NULL in Table 2, it has to take a ID from Table 1 and has to update its anme in Table 1.

(i.e The ID 3 has Name NULL, so it will be placed in Table 2, now it has to take a ID which is greater than 3 (Nearest to 3), and then it has to update the name filed in Table 1)

The Name (ID) has to selected based on the value in Table 2

Sudhakar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources