August 24, 2005 at 8:37 am
What is the best way to update 2 tables if a condition in one of the tables is met? What I want to do is update xtable after updating ytable if a certain condition is met.
For example, like
UPDATE xtable
SET xtable.ticket =
??? (UPDATE ytable SET ytable.NextTicket = ytable.NextTicket + 1)
WHERE xtable.ticket is null
I need to update the ytable.NextTicket prior to and during the update of xtable.ticket.
Thanks for your help in advance.
Phil
August 24, 2005 at 8:45 am
You need to do 2 separate updates.
August 26, 2005 at 2:46 am
Why not try something like this, it uses 2 updates but doesn't require a select to get the value of nextticket from #ytable:-
CREATE TABLE #xtable (ticket int)
CREATE TABLE #ytable (nextticket int)
INSERT INTO #xtable (ticket) VALUES (1)
INSERT INTO #xtable (ticket) VALUES (2)
INSERT INTO #ytable (nextticket) VALUES (2)
INSERT INTO #xtable (ticket) VALUES (NULL)
SELECT * FROM #xtable
SELECT * FROM #ytable
-- Do the actual work
DECLARE @lnNextticket int
UPDATE #ytable
SET @lnNextticket = nextticket = nextticket + 1
UPDATE #xtable
SET ticket = @lnNextticket
WHERE ticket IS NULL
-- end of work
SELECT * FROM #xtable
SELECT * FROM #ytable
DROP TABLE #xtable
DROP TABLE #ytable
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply