Another syntax worked better for me. I had added a Picture column to a table (Model), and wanted to update existing rows in the table with images from files - in this case Picture.jpg. The kicker was realising that you needed 2 aliases (PictureTable and blob).
WITH PictureTable AS (SELECT blob.* FROM OPENROWSET(BULK N'C:\Picture.jpg', SINGLE_BLOB) AS blob)
SET Model.Picture = PictureTable.BulkColumn
FROM Model, PictureTable
WHERE Model.Id = 2;