update values in datatool

Question

i try to update single values within a datatool DB. unfortunately, it only works for the first time. when i update the second values the first one will be also set to the value of the second one. when the third is updated, the first and second one are also set to the third value. same when i continue updating, it always uses the last value and changes all previously updated values to the value of the last one. what am i doing wrong here?

\documentclass{article}
\usepackage[utf8]{inputenc}


\usepackage{datatool}
\newcounter{searchID}


\begin{document}
\DTLnewdb{myDB}

  \DTLnewrow{myDB}  
  \DTLnewdbentry{myDB}{ID}{5}%
  \DTLnewdbentry{myDB}{counter}{2}%

  \DTLnewrow{myDB}  
  \DTLnewdbentry{myDB}{ID}{15}%
  \DTLnewdbentry{myDB}{counter}{3}%

  \DTLnewrow{myDB}  
  \DTLnewdbentry{myDB}{ID}{21}%
  \DTLnewdbentry{myDB}{counter}{6}%

orig tab

\DTLdisplaydb{myDB}
\bigskip

first update (of ID==5)

    \setcounter{searchID}{5}
    \DTLgetvalueforkey{\oldCounter}{counter}{myDB}{ID}{\thesearchID}%get current counter
    \xdef\newCounter{7} %dummy for some inc like this \newCounter = \oldCounter + 1
    \edtlgetrowforvalue{myDB}{\dtlcolumnindex{myDB}{ID}}{\thesearchID}
    \dtlupdateentryincurrentrow{counter}{\newCounter}% make local modification to current row
    \dtlrecombine% merge modification into database.
    \DTLdisplaydb{myDB}
    \bigskip


secund update (of ID==15)

    \setcounter{searchID}{15}
    \DTLgetvalueforkey{\oldCounter}{counter}{myDB}{ID}{\thesearchID}%get current cunter
    \xdef\newCounter{8} %dummy for some inc like this \newCounter = \oldCounter + 1
    \edtlgetrowforvalue{myDB}{\dtlcolumnindex{myDB}{ID}}{\thesearchID}
    \dtlupdateentryincurrentrow{counter}{\newCounter}% make local modification to current row
    \dtlrecombine% merge modification into database.
    \DTLdisplaydb{myDB}
    \bigskip


fthird update (of ID==21)

    \setcounter{searchID}{21}
    \DTLgetvalueforkey{\oldCounter}{counter}{myDB}{ID}{\thesearchID}%get current cunter
    \xdef\newCounter{9} %dummy for some inc like this \newCounter = \oldCounter + 1
    \edtlgetrowforvalue{myDB}{\dtlcolumnindex{myDB}{ID}}{\thesearchID}
    \dtlupdateentryincurrentrow{counter}{\newCounter}% make local modification to current row
    \dtlrecombine% merge modification into database.
    \DTLdisplaydb{myDB}

\end{document}

i have to use the \setcounter{searchID}{15} construction since i update the values within a foreach loop within tikz for a whole bunch of values. so i can not simply write the search value directly as argument to the macro.

Answer 1

\dtlupdateentryincurrentrow behaves like \dtlreplaceentryincurrentrow if there was already a value in the column. Note there is a warning for \dtlreplaceentryincurrentrow in the documentation:

The new value doesn’t get expanded.

So here is suggestion, but maybe there is a better way to expand the new value:

\documentclass{article}
%\usepackage[utf8]{inputenc}% needed for older TeX Distributions

\usepackage{datatool}
\newcounter{searchID}
\newcommand*\dtlupdatecounterincurrentrow
  {\dtlupdateentryincurrentrow{counter}}

\begin{document}
\DTLnewdb{myDB}

  \DTLnewrow{myDB}
  \DTLnewdbentry{myDB}{ID}{5}%
  \DTLnewdbentry{myDB}{counter}{2}%

  \DTLnewrow{myDB}
  \DTLnewdbentry{myDB}{ID}{15}%
  \DTLnewdbentry{myDB}{counter}{3}%

  \DTLnewrow{myDB}  
  \DTLnewdbentry{myDB}{ID}{21}%
  \DTLnewdbentry{myDB}{counter}{6}%

orig tab

\DTLdisplaydb{myDB}
\bigskip

first update (of ID==5)

    \setcounter{searchID}{5}
    \DTLgetvalueforkey{\oldCounter}{counter}{myDB}{ID}{\thesearchID}%get current counter
    \xdef\newCounter{7} %dummy for some inc like this \newCounter = \oldCounter + 1
    \edtlgetrowforvalue{myDB}{\dtlcolumnindex{myDB}{ID}}{\thesearchID}
    \expandafter\dtlupdatecounterincurrentrow\expandafter{\newCounter}% make local modification to current row
    \dtlrecombine% merge modification into database.
    \DTLdisplaydb{myDB}
    \bigskip

second update (of ID==15)

    \setcounter{searchID}{15}
    \DTLgetvalueforkey{\oldCounter}{counter}{myDB}{ID}{\thesearchID}%get current cunter
    \xdef\newCounter{8} %dummy for some inc like this \newCounter = \oldCounter + 1
    \edtlgetrowforvalue{myDB}{\dtlcolumnindex{myDB}{ID}}{\thesearchID}
    \expandafter\dtlupdatecounterincurrentrow\expandafter{\newCounter}% make local modification to current row
    \dtlrecombine% merge modification into database.
    \DTLdisplaydb{myDB}
    \bigskip

third update (of ID==21)

    \setcounter{searchID}{21}
    \DTLgetvalueforkey{\oldCounter}{counter}{myDB}{ID}{\thesearchID}%get current cunter
    \xdef\newCounter{9} %dummy for some inc like this \newCounter = \oldCounter + 1
    \edtlgetrowforvalue{myDB}{\dtlcolumnindex{myDB}{ID}}{\thesearchID}
    \expandafter\dtlupdatecounterincurrentrow\expandafter{\newCounter}% make local modification to current row
    \dtlrecombine% merge modification into database.
    \DTLdisplaydb{myDB}
\end{document}

Result: