====== pears.MoveTagValues ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
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
</code>