CODEWITHSUNDEEP
sqlsql-server-2008
user3538102
user3538102

Reputation: 171

iterating through cells within a column

I have a table which is full of values, however I want to manipulate this data in a temporary table! Below shows an example of the table (which is not my actual table)!

enter image description here

So say now I want to iterate through the row Name and where the name is john I want to change it to Simon and where the name is Carol I want to change to Mike (this is just an example to give me an idea of what is needed). The table below is also a temporary table that is being created so after it is created I want to go through the column! Below shows the code for creating the temporary table.

IF OBJECT_ID('tempdb..#LocalTempTable') IS NOT NULL
    DROP TABLE #LocalTempTablew


CREATE TABLE #LocalTempTable(
Source varchar(50), 
Value int,
Name varchar(150),
Date date)


insert into #LocalTempTable (Source, Value, Name, Date)
SELECT 
  table1.sourceID,
  table2.value,
  table1.name,
  table2.Timestamp
FROM         table1 INNER JOIN
                  Source ON table1.SourceID = table2.ID

SELECT Name, CASE Name
  WHEN 'John' THEN Name = 'Simon'
  WHEN 'Carol' THEN Name = 'Mike'
END
FROM #LocalTempTable

But when I run this query it is timing out!

Upvotes: 0

Views: 43

Answers (1)

Allan S. Hansen
Allan S. Hansen

Reputation: 4081

This doesn't need a cursor but should be able to do something like:

UPDATE T
SET Name = CASE Name 
            WHEN 'John' THEN 'Simon'
            WHEN 'Carol' THEN 'Mike'
            ELSE Name
          END
FROM Table1 T
<insert needed join and where clauses as needed>

Basically this updates the entire Table1, but you can make much more complex join conditions and WHERE in your FROM part of the update query; as long as you just update one table in the end.

Upvotes: 2

Related Questions