how to test for condition??

Question

Post reply

how to test for condition??

Kristin Stariha

Ten Centuries

Points: 1012
More actions
August 9, 2004 at 4:25 pm

#164580

Is it possible, rather, can someone explain to me how.... to test each record in a table for a condition?
For instance, I need to run through each record in a table and perform a query on it. This query will update another table.
I have a table Chains and a table Records in my database MROresults.
I need to run the update query (to Records) for each record in chains....
UPDATE records
SET chainpath = ?
WHERE (speciminid = ?)
Any ideas? Am I way out of the ballpark here? Is this possible? IT is sort of like I am using each record in one table as the parameters for my update query on another table.
Terrible confused with this issue... I know you can't perform an update query on two tables, but if I query one table, line by line, aren't I getting around it?
Any input or thoughts would be appreciated.

kristin

Kristin

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246993 More actions · Answer 1

I'm sure that we can help you out with just a bit more information.

1) What is the link between Records and Chains?

2) What do you want to set Records.Chainpath to?

3) What condition does SpeciminID have to satisfy?

You are correct in assuming that you cannot update more than one table at a time - though you can, in some circumstances, update a view which may update several tables on which it is based - gets a bit complicated to explain the circumstances though

However, the source data for the update can come from as many tables as needed.

eg

update r

set x = c.field1, y = c.field2 -- etc

from records r inner join chains c on r.id = c.id

where ...

Regards

Phil

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Kristin Stariha Ten Centuries Points: 1012 More actions · Answer 2

Hi Phil thanks for the reply..

1. The link between records and chains is the specimenID...actually, it is the field I would like to use to check for matches.

2. In the chain table, when a new entry is made, it is autonumbered. If a match is made (with the specimen ID's) I would like the record table to attach the Chain number as Chainpath(field)

3. The specimen ID has to match exactly. They are unique numbers.

Hope I explained that well enough, as you can see I am very new to all of this...

thanks for your input!

kristin

Kristin

Phil Parkin SSC Guru Points: 246993 More actions · Answer 3

Hi Kristin, no problem. I don't think I was too far away with my last attempt Here's a refined version (it assumes that SpecimenID is the link-field name in both tables, that 'chainnumber' is the autonumber field in the chains table and that 'chainpath' is the field in the records table that you wish to update):

update r

set chainpath = c.chainnumber

from records r inner join chains c on r.SpecimenID = c.SpecimenID

By performing the inner join, we ensure that only matching records are updated. It will update all records in 'records' which have matching records in 'chains'. If you intend to run the query repeatedly, you could add

where r.chainpath c.chainnumber

to the end of the query. This will avoid re-writing updates that have already been made and will therefore run faster.

Regards

Phil

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Kristin Stariha Ten Centuries Points: 1012 More actions · Answer 4

Hey Phil...when I run this it is asking me to declare r and c.....says variables not declared. What and where do I declare them?

kristin - I am missing something..

Kristin

vadba SSChampion Points: 11133 More actions · Answer 5

Kristin,

Try:

UPDATE records

SET chainpath = c.chainNumber

FROM records r JOIN chains c ON r.specimenID = c.specimenID

or, since the query is simple, just:

UPDATE records

SET chainpath = chains.chainNumber

FROM records JOIN chains ON records.specimenID = chains.specimenID

This assumes that the RECORDS table contains a column call chainpath, and that table CHAINS contains a column named chainNumber.

r and c are examples of a "local alias", a shorthand for the table name valid only within the query.

Mike

Kristin Stariha Ten Centuries Points: 1012 More actions · Answer 6

Thats it! thanks for all your help guys And it worked better yes, by just running the query as a stored procedure and calling it in VB! Looks simple I know, but I'm a novice, and this little update routine is going to save us all hours and hours of work here!

Here is what finally worked....thanks again!!!!!........Kristin

UPDATE records

SET records.chainpath = chains.chainnum

FROM records INNER JOIN

chains ON records.speciminid = chains.speciminid

Kristin