Update one table with another using a stored prcedure

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)

Update one table with another using a stored prcedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-23 : 09:58:50

Geoff writes "Hi,
I need to apply a set of standard names to the company names
stored in our Goldmine database. The STANDARDNAMES table
looks like this:


STANDARDNAME    GMNAME
=======================
...
IBM             IBM
IBM             IBM Corp
IBM             I.B.M.
...

A way to update one table with another might be this:


UPDATE dbo.CONTACT1
SET dbo.CONTACT1.COMPANY = dbo.STANDARDNAMES.STANDARDNAME
FROM dbo.STANDARDNAMES
WHERE
dbo.CONTACT1.COMPANY = dbo.STANDARDNAMES.GMNAME

Except, in this case the "SET ..." can't be done
- I need to use the GMW_WriteContact stored procedure
instead, otherwise I'll muck up the GoldMine database
in pretty short order. I have used the stored procedure,
but then I have to use the following algorithm which is
extremely inefficient: I have to run a SELECT statement
on the STANDARDNAME list once for every record in the
Goldmine database.

Can anyone suggest a better way?

Cheers,
Geoff.


OPEN CompanyCursor
FETCH CompanyCursor INTO @CompanyName, @TempRecID

/* Loop over all Goldmine Records */
WHILE (@@FETCH_STATUS=0) BEGIN

 /* Search the STANDARDNAMES GMNAME column for this Company name */
SELECT   @StandardName = dbo.STANDARDNAMES.Company, @Variation =
dbo.STANDARDNAMES.VARIATION
FROM   dbo.STANDARDNAMES
WHERE   (dbo.STANDARDNAMES.VARIATION = @CompanyName)

/* Update the Goldmine Company Name if there is a single match */
IF ((@@ROWCOUNT = 1) AND (@StandardName <> @CompanyName)) BEGIN
    EXEC @Status = dbo.GMW_WriteContact @gmnv
END
FETCH CompanyCursor INTO @CompanyName, @TempRecID
END

CLOSE CompanyCursor
DEALLOCATE CompanyCursor

dsdeming

479 Posts

Posted - 2002-08-23 : 10:03:35

You need to specify both tables in the from clause. I never alias the table being updated ( in the old days you couldn't, and I'm haven't even tried it in SQL2K ).

UPDATE dbo.CONTACT1
SET dbo.CONTACT1.COMPANY = s.STANDARDNAME
FROM dbo.CONTACT1
JOIN dbo.STANDARDNAMES s ON dbo.CONTACT1.COMPANY = s.GMNAME

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources