Hi, members I have written these routines to execute one after the other. But unfortunately, its not doing what i want it to do. I would like it to use a control structure to perform these routines one after the other and return the rows with the correct flags into the colourUpdate table. Attached below is the SQL script. Any help would be much appreciated. Thanks Albert
CREATE PROCEDURE colourUpdate
AS
/*Records that have not changed*/
BEGIN
INSERT INTO th_colourUpdate
SELECT DISTINCT t.DCRD, t.COM, t.COLOUR, t.FLAG
/*INTO th_colourUpdate*/
FROM tb_colour_ref As t
WHERE EXISTS
(SELECT *
FROM th_colourtest2 AS h
WHERE t.DCRD = h.DCRD AND
t.COM = h.COM AND
/* t.COLOUR = h.COLOUR AND*/
t.FLAG = h.FLAG)
Update th_colourUpdate
Set FLAG = 'U'
Where FLAG IS NULL
END
/* Deletions start from here*/
BEGIN
INSERT INTO th_colourUpdate
SELECT DISTINCT t.DCRD, t.COM, t.COLOUR, t.FLAG
FROM tb_colour_ref AS t
WHERE NOT EXISTS
(SELECT *
FROM th_colourtest2 AS h
WHERE t.DCRD = h.DCRD AND
t.COM = h.COM AND
/* t.COLOUR = h.COLOUR AND*/
t.FLAG = h.FLAG)
Update th_colourUpdate
Set FLAG = 'D'
Where FLAG IS NULL
END
/* Additions start from here*/
BEGIN
INSERT INTO th_colourUpdate
SELECT DISTINCT h.DCRD, h.COM, h.COLOUR, h.FLAG
FROM th_colourtest2 AS h
WHERE NOT EXISTS
(SELECT *
FROM tb_colour_ref AS t
WHERE h.DCRD = t.DCRD AND
h.COM = t.COM AND
/* h.COLOUR = t.COM AND*/
h.FLAG = t.FLAG)
Update th_colourUpdate
Set FLAG = 'A'
Where FLAG IS NULL
END
