Updating one table from another

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)

Updating one table from another

Author

Topic

steve_0710
Starting Member

3 Posts

Posted - 2007-04-11 : 10:56:07

I am new to stored procedures....Below is some code that updates one table based on another table. It works, but is this the most efficient way to do this. It seems to take a while to run.


if exists (SELECT company.cmp_primaryphone, company.cmp_secondaryphone, company.cmp_contact FROM  orderheader INNER JOIN company ON orderheader.ord_consignee = company.cmp_id WHERE orderheader.mov_number = @mov)
Begin

DECLARE @phone1 char(20)
DECLARE @phone2 char(20)
DECLARE @email char(30)

DECLARE c1 CURSOR FOR
SELECT company.cmp_primaryphone, company.cmp_secondaryphone, company.cmp_contact
FROM  orderheader INNER JOIN
               company ON orderheader.ord_consignee = company.cmp_id
WHERE (orderheader.mov_number = @mov)

OPEN c1

FETCH NEXT FROM c1
INTO @phone1, @phone2, @email

If @@FETCH_STATUS = 0
BEGIN
Update Orderheader
Set	ord_extrainfo7 = @phone1
	,ord_extrainfo8 = @phone2
	,ord_extrainfo9 = @email
	Where	mov_number = @mov


END

CLOSE c1
DEALLOCATE c1 
End

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-11 : 11:04:13

[code]
Update h
Set ord_extrainfo7 = c.phone1
,ord_extrainfo8 = c.phone2
,ord_extrainfo9 = c.email
from Orderheader h inner join company c
on h.ord_consignee = c.cmp_id
where h.mov_number = @mov
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources