Take the 2-minute tour ×

closed as off topic by Jack Douglas Nov 20 '12 at 5:41


Hi LinuxOwner, thanks for taking the trouble to post here. If @rfusca's answer is really what you need, then this is too basic to be on-topic here but please don't be put off posting advance SQL questions :) –  Jack Douglas Nov 20 '12 at 5:40

2 Answers 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

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)
SET @Name = (select top 1 NotOptimalDetail from #TempTbl)

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

SET @Id = ID + 1

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

share|improve this answer
+1 and thanks for the polite, helpful 'new user advice' for the OP—spot on. –  Jack Douglas Nov 20 '12 at 5:35
Thank you all for the help. I was unaware that this was the wrong section. I thouhgt who better to know about MySql Database stuff them people who are Database Administrators. Sorries, and again. thanks for the Help. –  LinuxOwner Nov 20 '12 at 6:04
Anytime Linux, and thanks Jack. the syntax for MySQL is what I am missing in my post, so take the above answer's syntax and modify my loop accordingly. It seems instead of using a concat (+) you use double pipes (||) in MySQL. If you dynamically pass the values in a loop it should work. –  Ali Razeghi Nov 20 '12 at 7:02

Seems like all you want is something like?

UPDATE banned SET evidence = 'http://path-to-something/'||name ;
share|improve this answer
To follow up on your suggestion, I think this would be the correct idea, but I was concerned that this would not do it for all the rows in the datatable. I think you for the assistance too. –  LinuxOwner Nov 20 '12 at 6:06
