Update Statement - 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

Transact-SQL (2000)

Update Statement

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-04-27 : 06:31:55

I've a Select Statement where i return data which i need to update another table with.

Here is my select Statement

SELECT dbo.tbl_Voy_RCATransfer.EmployeeID, dbo.tbl_Voy_RCATransfer.CaseID, dbo.tbl_Voy_RCATransfer.[Date], dbo.tbl_asp_current_ext.ext_num,dbo.tbl_asp_current_ext.team_number
FROM dbo.tbl_Voy_Employee INNER JOIN
dbo.tbl_asp_current_ext ON dbo.tbl_Voy_Employee.SPTelsetID = dbo.tbl_asp_current_ext.ext_num INNER JOIN dbo.tbl_Voy_RCATransfer ON dbo.tbl_Voy_Employee.EmployeeID = dbo.tbl_Voy_RCATransfer.EmployeeID)

I want to update my dbo.tbl_Voy_RCATransfer table with the data thats in the dbo.tbl_asp_current_ext.ext_num

Here is what am trying to do. I know this is not right but its only a example. I want the data thats in the col (ext_num) returned from the select put into another table call dbo.tbl_Voy_RCATransfer

Update dbo.tbl_Voy_RCATransfer
SET Aspect_id = ext_num
SELECT dbo.tbl_Voy_RCATransfer.EmployeeID, dbo.tbl_Voy_RCATransfer.CaseID, dbo.tbl_Voy_RCATransfer.[Date], dbo.tbl_asp_current_ext.ext_num,
dbo.tbl_asp_current_ext.team_number
FROM dbo.tbl_Voy_Employee INNER JOIN
dbo.tbl_asp_current_ext ON dbo.tbl_Voy_Employee.SPTelsetID = dbo.tbl_asp_current_ext.ext_num INNER JOIN dbo.tbl_Voy_RCATransfer ON dbo.tbl_Voy_Employee.EmployeeID = dbo.tbl_Voy_RCATransfer.EmployeeID

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-27 : 06:45:04

Somthing like this ..

Update dbo.tbl_Voy_RCATransfer
SET Aspect_id = ext_num From
(
SELECT dbo.tbl_Voy_RCATransfer.EmployeeID, dbo.tbl_Voy_RCATransfer.CaseID, dbo.tbl_Voy_RCATransfer.[Date], dbo.tbl_asp_current_ext.ext_num,
dbo.tbl_asp_current_ext.team_number
FROM dbo.tbl_Voy_Employee INNER JOIN
dbo.tbl_asp_current_ext ON dbo.tbl_Voy_Employee.SPTelsetID = dbo.tbl_asp_current_ext.ext_num INNER JOIN dbo.tbl_Voy_RCATransfer ON dbo.tbl_Voy_Employee.EmployeeID = dbo.tbl_Voy_RCATransfer.EmployeeID
)as F Inner join dbo.tbl_Voy_RCATransfer On F.EmployeeId = tbl_Voy_RCATransfer

???

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-04-27 : 07:01:16

This worked fine, just one small thing, on the last line you
had
as F Inner join dbo.tbl_Voy_RCATransfer On F.EmployeeId = tbl_Voy_RCATransfer
i changed it to, i assume this is correct ?
as F Inner join dbo.tbl_Voy_RCATransfer On F.EmployeeId = tbl_Voy_RCATransfer.EmployeeId

Can you Explain, this line as am not 100% sure about it, thanks.

Update dbo.tbl_Voy_RCATransfer
SET Aspect_id = ext_num From
(
SELECT dbo.tbl_Voy_RCATransfer.EmployeeID, dbo.tbl_Voy_RCATransfer.CaseID, dbo.tbl_Voy_RCATransfer.[Date], dbo.tbl_asp_current_ext.ext_num,
dbo.tbl_asp_current_ext.team_number
FROM dbo.tbl_Voy_Employee
INNER JOIN dbo.tbl_asp_current_ext ON dbo.tbl_Voy_Employee.SPTelsetID = dbo.tbl_asp_current_ext.ext_num
INNER JOIN dbo.tbl_Voy_RCATransfer ON dbo.tbl_Voy_Employee.EmployeeID = dbo.tbl_Voy_RCATransfer.EmployeeID
)as F Inner join dbo.tbl_Voy_RCATransfer On F.EmployeeId = tbl_Voy_RCATransfer.EmployeeId

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-04-27 : 07:09:01

opps.. yeah its right i just forgot to add at the end..

The query which you supplied initially from where you wanted to update the records i made it as a table..
i.e. SELECT dbo.tbl_Voy_RCATransfer.EmployeeID, dbo.tbl_Voy_RCATransfer.CaseID, dbo.tbl_Voy_RCATransfer.[Date], dbo.tbl_asp_current_ext.ext_num,
dbo.tbl_asp_current_ext.team_number
FROM dbo.tbl_Voy_Employee
INNER JOIN dbo.tbl_asp_current_ext ON dbo.tbl_Voy_Employee.SPTelsetID = dbo.tbl_asp_current_ext.ext_num
INNER JOIN dbo.tbl_Voy_RCATransfer ON dbo.tbl_Voy_Employee.EmployeeID = dbo.tbl_Voy_RCATransfer.EmployeeID

and named it as F ..

Now if you see in the select query employeeid is from the table where you want to update the statement.. so i just joined both of them.. ..

if you are only going to update this field then use following query ..

Update dbo.tbl_Voy_RCATransfer
SET Aspect_id = ext_num From
(
SELECT dbo.tbl_Voy_RCATransfer.EmployeeID, dbo.tbl_asp_current_ext.ext_num
FROM dbo.tbl_Voy_Employee
INNER JOIN dbo.tbl_asp_current_ext ON dbo.tbl_Voy_Employee.SPTelsetID = dbo.tbl_asp_current_ext.ext_num
INNER JOIN dbo.tbl_Voy_RCATransfer ON dbo.tbl_Voy_Employee.EmployeeID = dbo.tbl_Voy_RCATransfer.EmployeeID
)as F Inner join dbo.tbl_Voy_RCATransfer On F.EmployeeId = tbl_Voy_RCATransfer.EmployeeId

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources