I am doing an update procedure where I am running the first update and then the second update statement only needs to update the records modified by the previous one. So I wanna specify in the where clause that it needs to update only the records where the LastUpdate Time and Date is within the last 2 minutes or 1 minute.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How do I subtract (1 to 10 minutes) from current time? 1
- Thread starter belcom125
- Start date
belcom
Sound slike a risky what of doing the udates, what happens is a user updates a field between the 2 or if you don't manage to run update 2 within the required time scale etc etc.
Would be a lot safer if you could to get update 1 to set a flag then get update 2 to re-set it when it runs, that way any problems about timings would be removed.
Sound slike a risky what of doing the udates, what happens is a user updates a field between the 2 or if you don't manage to run update 2 within the required time scale etc etc.
Would be a lot safer if you could to get update 1 to set a flag then get update 2 to re-set it when it runs, that way any problems about timings would be removed.
- Thread starter
- #3
Yes I know that it is not the most proper way. But I am getting frustrated here. I've been working on this task for a like a week now. Which seems like a VERY long time. And right now I just wanna hand it in as long as it works. I am kinda an intermediate SQL programmer however sometimes I just don't see the logic in some things.
When you say flag, do you mean another yes/no field that I have to add? Cause that would be a problem I think. Because it's a third party application. I am not sure how it could screw things up.
When you say flag, do you mean another yes/no field that I have to add? Cause that would be a problem I think. Because it's a third party application. I am not sure how it could screw things up.
-
1
- #5
Stick to your guns dhulbert. The potential for subtle errors to creep in outway the quick and dirty solution. The whole update needs to be reconsidered. For a "safe" quick and dirty solution, I'd create another table that adds the Yes/No column and the PK from the original table so that a join can be done.
Belcom125, I hear the frustration loud and clear, but don't compromise the integrity of your solution. It will come back to haunt you.
-Karl
[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
Belcom125, I hear the frustration loud and clear, but don't compromise the integrity of your solution. It will come back to haunt you.
-Karl
[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
donutman is correct. You could end up with a solution that appears to work but have data integrity problems later on. A half-assed solution is often worse than no solution at all. Unless you are positive that users can't be on the system changing records in that table, then do not perform updates for records changed in the last 2 minutes.
If you are updating two different fields, then put them both in one statement and the where clause will filter the records the smae for both fields. If you are updating the same field, what is the point? Why not just go directly to the second value? Donutman's idea about a joining table is a good one if you must do two updates. It could even be a temp table or table variable within the stored procedure and not affect the actual database structure at all.
Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief
If you are updating two different fields, then put them both in one statement and the where clause will filter the records the smae for both fields. If you are updating the same field, what is the point? Why not just go directly to the second value? Donutman's idea about a joining table is a good one if you must do two updates. It could even be a temp table or table variable within the stored procedure and not affect the actual database structure at all.
Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief
hi,
@ SQLSISTER, just a question (maybe silly) If stored procedure with table variable is used, shouldn't both updates be in a same batch. what if after first update SP goes out of scope? Isn't it better to use ##temp I mean Global temp table for this purpose?
maybe im missing something but its a good opportunity to clear my mind. {:.}
B.R,
miq
@ SQLSISTER, just a question (maybe silly) If stored procedure with table variable is used, shouldn't both updates be in a same batch. what if after first update SP goes out of scope? Isn't it better to use ##temp I mean Global temp table for this purpose?
maybe im missing something but its a good opportunity to clear my mind. {:.}
B.R,
miq
Personally I would do both actions in the same sp and use a local scope table variable or local temp table. I'm not too fond of global temp tables because they are visible to other sessions.
Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief
Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief
Hmmm...St. Bernard or Queen Bee. A little territorial are we? (j/k of course) 
-Karl
[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
-Karl
[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]
- Thread starter
- #10
maybe you guys can help me solve the problem at the root then? I posted recently another topic "the problem itself". I guess you know how to find the post. So please have a look at the description an the code I am using and the problem I am having. I'd appreciate another opinion on this.
- Thread starter
- #13
- Thread starter
- #14
- Status
Similar threads
- Locked
- Question
