I’m using MS SQL Server 2019
I have a string in a table (tblJobCosts) that has its own ID like this:
TextID jobText 1 Total Cost for job is £. This includes VAT
How do I update the value stored in the brackets based on the value from another table?
The end result would look like this:
Total Cost for job is £500. This includes VAT
I thought I could incorporate a SELECT with a REPLACE but this does not seem possible:
DECLARE @JobNum INT = 123; UPDATE dbo.JobCosts SET jobText = REPLACE (jobText,'', SELECT JH.jobCost FROM dbo.JobHead AS JH WHERE (JH.JobNo = @JobNum) ) AND TextID = 1
If I run the above I receive the error:
Incorrect syntax near the keyword 'SELECT'.
Is it possible to incorporate a SELECT with a REPLACE?
I think that you cannot call a select statement in the replace function.
I would try something like that:
UPDATE dbo.JobCosts SET jobText = REPLACE (jobText,'',k.the_cost) from ( SELECT JH.jobCost as the_cost FROM dbo.JobHead AS JH WHERE (JH.JobNo = @JobNum) )k where TextID = 1