Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to update table if SMS is available in other table here my code
SQL
update tblSMSSendData set tblSMSSendData.SMSCAMPID=(select dbo.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*')

and error is
Msg 512, Level 16, State 1, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.


What I have tried:

SQL
update tblSMSSendData set tblSMSSendData.SMSCAMPID=(select dbo.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*')
Posted
Updated 9-Mar-17 23:16pm
v2
Comments
Tomas Takac 9-Mar-17 3:48am    
What about doing it in two steps? First read tblCampaignMaster.ID into a variable and then use that variable to update tblSMSSendData.SMSCAMPID.

First read tblCampaignMaster.ID into a variable and then use that variable to update tblSMSSendData.SMSCAMPID.
SQL
declare @id int

select @id=ID from dbo.tblCampaignMaster cm
where cm.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*'

update tblSMSSendData set SMSCAMPID=@id
 
Share this answer
 
v2
Comments
Member 12962919 9-Mar-17 3:58am    
its not work

Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "tblSMSSendData.SMSTEXT" could not be bound.
Tomas Takac 9-Mar-17 4:08am    
Just to be sure I added the dbo schema - check the updated solution.
Hello,

Avoid use of Sub-query here as multiple values return from your condition.

Try Update From statement as shown below:

SQL
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.
 
Share this answer
 
Comments
Member 12962919 10-Mar-17 7:10am    
Thanks, but the problem is already solved

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900