By design, siunitx uses \bfseries and \boldmath when detect-weight is in force, but we need \fontseries{b} instead and no boldening of the \pm sign. Solution: locally redefine the two commands.
\documentclass{article}
\usepackage{siunitx,etoolbox}
\begin{document}
\begin{table}
\sisetup{
table-align-uncertainty=true,
separate-uncertainty=true,
}
%% local redefinitions
\renewrobustcmd{\bfseries}{\fontseries{b}\selectfont}
\renewrobustcmd{\boldmath}{}
\begin{tabular}{
S[table-format=3.3(3),detect-weight,mode=text]
S[table-format=3.3(3),detect-weight,mode=text]
}
{Header} & {Header}\\
100.531 \pm 0.09 & \bfseries 50.531 \pm 0.09 \\
\bfseries 12.531 \pm 0.095 & 12.531 \pm 0.09 \\
\end{tabular}
\end{table}
\end{document}

Note that siunitx assumes the uncertainty to have single digit integral part. If you really need more complex uncertainty values, I think the only way is to use more columns.
\documentclass{article}
\usepackage{siunitx,etoolbox}
\begin{document}
\begin{table}
\sisetup{
table-align-uncertainty=true,
separate-uncertainty=true,
}
%% local redefinitions
\renewrobustcmd{\bfseries}{\fontseries{b}\selectfont}
\renewrobustcmd{\boldmath}{}
\begin{tabular}{
S[table-format=3.3,detect-weight,mode=text]
@{${}\pm{}$}
S[table-format=2.3,detect-weight,mode=text]
S[table-format=3.3,detect-weight,mode=text]
@{${}\pm{}$}
S[table-format=2.3,detect-weight,mode=text]
}
\multicolumn{2}{c}{Header} & \multicolumn{2}{c}{Header}\\
100.531 & 10.09 & \bfseries 50.531 & \bfseries 10.09 \\
\bfseries 12.531 & \bfseries 0.095 & 12.531 & 0.09 \\
\end{tabular}
\end{table}
\end{document}
