Can the diffraction limit be overcome with superresolution techniques?

Sort of, to a limited degree. Using sub-pixel shifting of the imaging sensor, in effect you are increasing each pixel size while keeping their spacing the same. Of course, it is not physically possible to build sensors where individual pixels are larger than their pitch (center-to-center spacing). But mathematically, this is basically what's happening.

That sounds great, but how does that overcome diffraction limits?

As Michael Clark stated in his answer, a camera system is diffraction limited when the size of the Airy disk (the blur) caused by diffraction becomes larger than the size of a digital camera's sensor pixels.

The size and nature of the Airy disk is not something you can overcome — it's a function of the wave-like behavior of light, the aperture size (usually assumed to be circular), and the wavelength of the particular light in question).

But if you can increase the size of the pixels while still packing the same number of pixels in the same area, you can "push back" the diffraction limit a bit farther. And that's what sub-pixel shifting of the image sensor does.

So it's not overcoming the diffraction limit per se, it's more like moving the goalposts a little bit.

The upper limit of sub-pixel shifting superresolution is an apparent twofold increase in resolution.

You don't get something for nothing. What's the tradeoff?

Well, as you mentioned, it requires a non-moving subject, that's one of the limits of applicability. As John stated in his answer, you are using the temporal-based certainty (i.e., there is no motion in the scene, so it exists independent of time) to take multiple images (which takes time, but who cares, you have plenty of it when the subject isn't moving) that help you increase your spatial information / knowledge about the scene.