To change a file in place, use sed -i:
$ echo "AAA" > tmp.txt
$ sed -i 's/A/B/g' tmp.txt
$ cat tmp.txt
BBB
The above uses GNU sed syntax. If you are using Mac OSX (BSD), use:
sed -i '' 's/A/B/g' tmp.txt
Discussion
From the question, consider this line of code:
cat tmp.txt | sed 's/A/B/g' > tmp.txt
cat tmp.txt attempts to read from tmp.txt. When the shell sees > tmp.txt, however, it truncates tmp.txt to an empty file in preparation for input. The result of something like this is not reliable.
sed -i by contrast was explicitly designed to handle this situation. It completely avoids the conflict.
If you like, sed -i can create a back-up of the original file. With GNU sed, use:
sed -i.bak 's/A/B/g' tmp.txt
With BSD (Mac OSX) sed, add a space:
sed -i .bak 's/A/B/g' tmp.txt