CODEWITHSUNDEEP
sqlsql-server
Shane
Shane

Reputation: 542

Create a temporary table and loop through the rows in order to perform an update on another table

I'm trying to alter data from an existing table into a temporary table and loop through the rows in order to perform an update on existing table data.

I need to strip down the DeviceName column and cast as an INT in order to extract the DevicesID's that will be needed in order to perform an update on an existing table. The first table shows an example of the unformatted DeviceName. The second table shows an example of the formated DeviceName (DeviceIntModified). I then need to get those DevicesID's so I can use them to perform an update on an existing table.

My original table tblDevices:

----------------------------------------------------
DeviceName          |   DevicesID   |   StationID
----------------------------------------------------
MainStation-464     |   1163        |   14
MainStation-465     |   1164        |   14
MainStation-466     |   1165        |   14
MainStation-467     |   1166        |   14
----------------------------------------------------

WITH tempTable as 
(
   SELECT
      DevicesID,
      RIGHT(DeviceName, LEN(DeviceName) - 12) AS DeviceNameModidfied
   FROM
      tblDevices 
   where
      StationID = 14 
)
select
   CAST (DeviceNameModidfied as int) as DeviceIntModified,
   DevicesID 
from
   tempTable 
where
   DeviceNameModidfied > 464 
   AND DeviceNameModidfied < 467

This works great and results in the following newly created tempTable:

----------------------------------
DeviceIntModified   |   DevicesID
----------------------------------
        465         |   1164
        466         |   1165
----------------------------------

I'm having issues selecting data from this tempTable and looping through the data. 

DECLARE @RowCount INT WITH tempTable as 
(
   SELECT
      DevicesID,
      RIGHT(DeviceName, LEN(DeviceName) - 9) AS DeviceNameModidfied 
   FROM
      tblDevices 
   where
      StationID = 14 
)
select
   CAST (DeviceNameModidfied as int) as DeviceIntModified,
   DevicesID 
from
   tempTable 
where
   DeviceNameModidfied > 464 
   AND DeviceNameModidfied < 500 
SET
   @RowCount = 
   (
      SELECT
         COUNT(DevicesID) 
      from
         tempTable
   )
   DECLARE @I INT 
SET
   @I = 1 WHILE (@I <= @RowCount) 
   BEGIN
      DECLARE @rowID INT 
      --I'm getting invalid column/object name when trying to select from tempTable here
      SELECT
         @rowID = DevicesID 
      from
         tempTable 
         UPDATE
            anotherTable                
            SET CheckBox = 'T'
         WHERE
            DevicesID = @rowID 
         SET
            @I = @I + 1 
   END

The results should look something like this: anotherTable:

----------------------------------
    CheckBox        |   DevicesID
----------------------------------
        T           |   1164
        T           |   1165
----------------------------------

Upvotes: 0

Views: 1285

Answers (2)

Sean
Sean

Reputation: 573

You can update a CTE directly, but in this case you can simply use an exists clause. The join criteria may be different if your example isn't exact. The exists clause is used over an inner join because you don't need to view the returned records which leads to this approach being preferable. 

WITH tempTable as 
(
   SELECT
      DevicesID,
      RIGHT(DeviceName, LEN(DeviceName) - 12) AS DeviceNameModidfied
   FROM
      tblDevices 
   where
      StationID = 14 
)
, TempTable_2 as (
select
   CAST (DeviceNameModidfied as int) as DeviceIntModified,
   DevicesID 
from
   tempTable 
where
   DeviceNameModidfied > 464 
   AND DeviceNameModidfied < 467
)
Update SomeOtherTable
Set CheckBox = 'T'
where exists (select 1 from TempTable_2 tt where DeviceID = tt.DeviceID);

Upvotes: 2

pwilcox
pwilcox

Reputation: 5753

You can avoid looping and even CTE's if you use the update syntax that allows you to join to 'anotherTable' right in the query itself.

update      other.checkbox = 't'
from        anotherTable other 
join        tblDevices td on td.devicesId = other.devicesId
cross apply (select DeviceNameMod = convert(int,right(DeviceName, len(DeviceName) - 12))) ap
where       td.StationID = 14 
and         ap.DeviceNameMod > 464 
and         ap.DeviceNameMod < 500;

But if you were to continue with your original approach, one of your big problems is that your @rowID is being used like an iterator, which should be from 0 to n-1 or from 1 to n, but you're populating it with 'DevicesId', which most certainly doesn't work that way.

Upvotes: 0

Related Questions