SQL Insert Problem

[emblewembl]

I have a stored procedure which is called from a web site at say every 2 minutes which returns a set of rows of data about entries in a race. If one of the fields in any of the entries is set to 1 rather than 0 I would like to perform the following actions:

1. Get the related id for the entry.
2. Extract some data from a view for this id.
3. Update another table with the new data.
4. Set the initial field back to 0 so that once the update has been done it won't be done again.

In my initial select statement it's as though i want to have something like:

select m.id, m.b, m.c (case when m.c = 1 then 
    update myTable
    set col = valCalculated
    where id = m.id)
from mainTable m
where raceid = 12345

but i know i can' embed an update statement like this. Incidentally there may be more than one row with m.c set to 1 in each race.

I am stuck... can anyone help?

i love chocolate

 

[jbenson001]

Sounds like a job for a trigger(1,3,4) and a select for #2.

Jim

 

[SQLSister]

If you need to do steps in your code based on whether something exists or not then use the if statement not the case. Example{code]
if (select codefield from table1) = 1
Begin
Perform an insert or update here
End
Else
Begin
Do something different
End

select what you need now that the tables have the data
{/code]

Questions about posting. See faq183-874
Click here to help with Hurricane Relief