There is no 'elegant' way to do this, but there are a couple of different ways that this can be done.
The most certain one that I can code uses a cursor:
Create Procedure usp_UniquelyIdentify
AS
declare c_hID CURSOR For
Select Household_ID FROM Household
Declare @hID int
Declare @lastID int
Declare @uhID int
Set @LastID = -1
Open c_HID
Fetch Next from c_HID INTO @hID
While @@FETCH_STATUS = 0
BEGIN
If @LastID <> @hID
BEGIN
Set @uhID = 1
Set @LastID = @hID
END
ELSE
Set @uhID = @uhID + 1
Update Household Set re_household_id = Cast(@hID as varchar) + '-' + Cast(@uhID As Varchar)
WHERE CURRENT OF c_HID
Fetch Next from c_HID INTO @hID
END
Close c_HID
Deallocate c_HID
Run the SP, and the re_household_id field should be populated as you need it to be.