pears.MoveTagValues

CREATE PROCEDURE "pears"."MoveTagValues"( IN @TagLocation CHAR(3),@TagID CHAR(3),@FromTagChoiceID CHAR(4),@TotagChoiceID CHAR(4),@RemoveOld CHAR(1) ) 
BEGIN
  /* Where both old and new values are present update the new one with the values from the old */
  UPDATE "TagValue" AS "NewTV" JOIN "TagValue" AS "OldTV"
    ON "NewTV"."TagLocation" = "OldTV"."TagLocation"
    AND "NewTV"."TagID" = "OldTV"."TagID"
    AND "NewTV"."TagChoiceID" = @ToTagChoiceID
    AND "OldTV"."TagChoiceID" = @FromTagChoiceID
    AND "NewTV"."TagID" = @TagID
    AND "NewTV"."TagLocation" = @TagLocation
    SET "NewTV"."Value" = "greater"("NewTV"."Value","OldTV"."Value"),
    "NewTV"."TextValue" = "trim"("string"("NewTV"."TextValue",' ',"OldTV"."TextValue"));
  /* Where no record exists with the new tagchoice change the existing record to the new tagchoice */
  UPDATE "TagValue" SET "TagValue"."TagChoiceID" = @ToTagChoiceID
    WHERE "TagValue"."TagLocation" = @TagLocation
    AND "TagValue"."TagID" = @TagID
    AND "TagValue"."TagChoiceID" = @FromTagChoiceID
    AND NOT EXISTS(SELECT "id" FROM "TagValue" AS "tv"
      WHERE "tv"."ID" = "TagValue"."ID"
      AND "tv"."TagID" = @TagID
      AND "tv"."TagLocation" = @TagLocation
      AND "tv"."TagChoiceID" = @ToTagChoiceID);
  /* Remove old tagvalues */
  DELETE FROM "TagValue"
    WHERE "TagValue"."TagLocation" = @TagLocation
    AND "TagValue"."TagID" = @TagID
    AND "TagValue"."TagChoiceID" = @FromTagChoiceID;
  /* If required - remove old choice */
  IF @RemoveOld = 1 THEN
    /* First update any references in stored searches / vacancy requirements */
    UPDATE "Criterion"
      SET "Criterion"."TagChoiceID" = @ToTagChoiceID
      WHERE "Criterion"."SourceLocation" = @TagLocation
      AND "Criterion"."TagID" = @TagID
      AND "Criterion"."TagChoiceID" = @FromTagChoiceID
      AND "isnull"("Criterion"."DictionaryID",'') = '';
    /* Then remove the old choice*/
    DELETE FROM "TagChoice"
      WHERE "TagChoice"."TagLocation" = @TagLocation
      AND "TagChoice"."TagID" = @TagID
      AND "TagChoice"."TagChoiceID" = @FromTagChoiceID
  END IF
END