Hello,
Avoid use of Sub-query here as multiple values return from your condition.
Try Update From statement as shown below:
UPDATE tblSMSSendData
SET tblSMSSendData.SMSCAMPID= tblCampaignMaster.tblCampaignMaster.ID
FROM dbo.tblCampaignMaster
WHERE tblSMSSendData.SMSTEXT=
'HERO City Motors Mega Scooter offer till 20th July, Low Down Payment Rs.3500. ROI 12%, FREE Helmet,
Insurance, Exchange Bonus & Referal Gift - 9865209696*'
Please let me know if the solution doesn't suffice your requirement.