CODEWITHSUNDEEP
c#sqlsql-serverinsert
rahulchawla
rahulchawla

Reputation: 296

Insert values for column based on values of separate column (C#)

My table currently looks like this

 ID    NAMESITE SENSORTYPE ____(columns between) SENSORNUMBER(initialized to all null)
 ---------------------------------------------------------------
 SW02       random                                null--
 SW02       camera                                null--
 SW02       random                                null
 SW02       camera                                null
 NW02       RANDOm                                null
 NW02       camera                                null
 NW02       random                                null
 NW02       camera                                null
 NW02       camera                                null

Basically what I want to do is increment the sensor number if it comes in contact with a sensor type = 'camera' and if it comes in contact with 2 id's that are the same it should input the incremented value in the same row for sensor number HOWEVER all the ID-Sensor type which are NOT camera's can be left null

An example output should be like this

ID    NAMESITE SENSORTYPE ____(columns between) SENSORNUMBER(initialized entire Column NULL)
----------------------------------------------------------------------- 
SW02           random                              null--
SW02           camera                                 1
SW02           random                              null
SW02           camera                                 2
NW02           RANDOm                               null
NW02           camera                                 1
NW02           random                               null
NW02           camera                                 2
NW02           camera                                 3

I'm not sure how I want to accomplish this with C# or without and I'm not sure how to approach this, I assume I'll need to keep a running count any help ?

Please note I want to modify my EXISTING TABLE not create a new table(the first one is what my initial table looks like)

where SENSORTYPE = 'Camera' should be incremented if more then one of the same ID.

Upvotes: 0

Views: 59

Answers (2)

rahulchawla
rahulchawla

Reputation: 296

To get a sequential implementation for the SENSOR number starting at 1(NOT ARBITRARLY) I just created another row called rownum which kept track of the row#(1-endrow) and used the order by that so it wasn't using arbitrary numbers. - If anyone was wondering!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

I think you can do this with an update and CTE:

with toupdate as (
      select t.*, row_number() over (partition by id order by id) as seqnum
      from t
      where sensortype = 'Camera'
     )
update toupdate
    set sensornumber = seqnum;

Note: Your sensor numbers are increasing for each id. But there is no (obvious) column for ordering the values. If you have such a column, then the order by should use that column. Otherwise, the values will be sequential but arbitrarily ordered.

Upvotes: 1

Related Questions