How to update an SQL column using a known perm link while appending a value from another column within the same table? [closed]

Question

Larger view of the issue is here: http://i1268.photobucket.com/albums/jj572/LinuxOwner/question.png

Answer 1

Seems like all you want is something like?

UPDATE banned SET evidence = 'http://path-to-something/'||name ;

Answer 2

Welcome to StackExchange. This is a great site to get help, and thank you for the picture, it was helpful, however it always helps if you put in an extra bit of effort when asking for a question so it doesn't get voted down. Now on to the question.

In MS SQL Server (using TSQL, but you can do it with ANSI SQL as well) I would create a temp table with a row ID so I could while loop through it. Then I would select each row, pass in the row info as a variable, and dynamically generate a query or just execute it. I would recommend dynamically generating the query, testing it, then running it.

Step 1: Populate the column with the 'path to something'

Step 2:

select ID, NAME, evidence
INTO #TempTbl
from TblName

Step 3: Create a while loop to go through each row and dynamically assign the value in "name" to append to the end of the "evidence" line

DECLARE @LoopId INT
SELECT @LoopId = 1
WHILE @LoopId < (Select COUNT(*) +1 FROM #TempTbl)


DECLARE @Id nvarchar(10)
SET @Id = (SELECT TOP 1 id from #TempTbl)
DECLARE @Evidence NVARCHAR(rowlength)
SET @Notes = (SELECT TOP 1 Evidence from #TempTbl)
DECLARE @Name NVARCHAR(max)
SET @Name = (select top 1 NotOptimalDetail from #TempTbl)

DECLARE @SQLCMD NVARCHAR(max)
SET @SQLCMD=
'UPDATE DbName.dbo.TblName
SET Evidence = ' + ''''+ @Evidence + '\' +  @Name + '''' + '
WHERE id = ' + @Id 
EXEC (@sqlcmd)

DELETE FROM #TempTbl WHERE @Id = ID
SET @Id = ID + 1

Make your modifications where needed to get it to run in MySQL but it should be very similar.