what is wrong with this stored procedure? 1

[jgoodman00]

I have managed (finally!) to write the following stored procedure which will execute:
CREATE PROCEDURE StoredProcedure1 AS
UPDATE ADOPT
SET ADOPT.BrsID = tblBrsCodes.BrsID
FROM ADOPT, tblBrsCodes
WHERE tblBrsCodes.BrsCode = ADOPT.brsno
AND tblBrsCodes.road = ADOPT.road

GO

It references two tables, ADOPT & tblBrsCodes. Adopt currently contains fields called BrsId, brsno & road. tblBrsCode currently contains fields called BrsID, BrsCode, Road.

For each record in adopt I want to insert the correct BrsID, based upon its brsno & road. These are comparable to tblBrsCode.BrsCode & tblBrsCode.road. tblBrsCode is the lookup table which contains the corresponding BrsId number that I want filled in for every record contained in adopt. This is incredibly simple in Access (See other question), but appears to be almost impossible in SQL server.

Any suggestions?? James Goodman
j.goodman00@btinternet.com

 

[bassguy]

set ADOPT.BrsID = select(tblBrsCodes.BrsID
FROM ADOPT, tblBrsCodes
WHERE tblBrsCodes.BrsCode = ADOPT.brsno
AND tblBrsCodes.road = ADOPT.road)

should do it

bassguy

 

[bassguy]

OOOPS
i mean

ADOPT.BrsID = (select tblBrsCodes.BrsID
FROM ADOPT, tblBrsCodes
WHERE tblBrsCodes.BrsCode = ADOPT.brsno
AND tblBrsCodes.road = ADOPT.road)

 

[tlbroadbent]

If it works, what is the question? I'm unclear what you need to know. I would recommend the following changes for the reasons noted.

CREATE PROCEDURE StoredProcedure1 AS

UPDATE ADOPT

-- Remove the table name from the column you are setting.
-- SQL 7 and above will error whe the table name is
-- included in the SET statement.

SET BrsID = tblBrsCodes.BrsID

-- Use ANSI standard JOIN syntax rather than the older
-- syntax where the JOIN criteria is declared in the WHERE
-- clause. The older syntax may return erroneous results
-- may not be supported in future versions of SQL Server.

FROM ADOPT INNER JOIN tblBrsCodes

-- My personal preference is to list the criteria in the
-- order the tables appear in the query. This improves
-- readability and aids understanding the query.

ON ADOPT.brsno = tblBrsCodes.BrsCode
AND ADOPT.road = tblBrsCodes.road
GO

BTW, Access creates queries with ANSI SQL JOIN syntax! Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[ababbitt]

Try this.

CREATE PROCEDURE StoredProcedure1 AS
UPDATE ADOPT
SET ADOPT.BrsID = (Select tblBrsCodes.BrsID
FROM ADOPT, tblBrsCodes
WHERE tblBrsCodes.BrsCode = ADOPT.brsno
AND tblBrsCodes.road = ADOPT.road)

GO

 

[jgoodman00]

I understand the reasons for changing it to this fully. This also passes the syntax check with no trouble. However, it does not actually update the records contained in ADOPT with their corresponding BrsID.

I was going for my usual, accidentaly vagueness with my first post!

Basically I want to join the ADOPT & tblBrsCodes tables by ADOPT.brsno = tblBrsCodes.BrsCode AND ADOPT.road = tblBrsCodes.road

ADOPT currently contains approx 15000 records, & all contain a brsno, road, station. I have created a second table (tblBrsCodes) which will now store brsno,road,station etc, to remove duplication of data. As a result I need to insert the corresponding BrsId from tblBrsCode. As I stated in another post, this is easy to do in Access, but appears much harder to achieve in SQL Server.

Does this clarify what I need to do? If so, can anyone help?

James Goodman
j.goodman00@btinternet.com

 

[jgoodman00]

OK, if this were another attempt at landing a craft on Mars it definately would have crashed. I had not noticed that ADOPT.brsno was empty. Hence it never actually altered any records....

In the words of someone far more famous, D'Oh! James Goodman
j.goodman00@btinternet.com