insert values into a table's column and update that column

Question

I want to insert values into a table's column and for every value inserted I want to copy all the rows `wHERE anotherColumn ='someString'

Then I want to update the id of the rows inserted based on the name they have (rN).

Table definition:

myFirstTable

rN      rID   rnk        dateR        value1 value2 calculation1 calculation2 calculation3
abc_3m   3   abc_3mxfx  20.10.2010     1       3      4            3             0.33
abc_6m   4   abc_6mxfx  20.10.2010     2       1      3            8             4

First, let's insert new values in rN. Values taken where rN = abc_6m

insert into #myFirstTable (rN)
values 
('abc_6m'), ('abc_1y'), ('abc_2y'), ('abc_3y'), ('abc_4y'), ('abc_5y'), ('abc_7y'), ('abc_10y'), ('abc_15y'), ('abc_30y')

query 1

update #myFirstTable
set rN = (select * from #myFirstTable
where rN = 'abc_6m')
where rN in (
             'abc_6m', 'abc_1y', 'abc_2y', 'abc_3y', 'abc_4y', 'abc_5y', 'abc_7y', 'abc_10y', 'abc_15y', 'abc_30y'
             )

The table would become:

#myFirstTable
rN      rID   rnk        dateR        value1 value2 calculation1 calculation2 calculation3
abc_3m   3   abc_3mxfx  20.10.2010     1       3      4            3             0.33
abc_6m   4   abc_6mxfx  20.10.2010     2       1      3            8             4
abc_1y   4   abc_6mxfx  20.10.2010     2       1      3            8             4
abc_2y   4   abc_6mxfx  20.10.2010     2       1      3            8             4

and so on, for all the values inserted.

then i want to update the rID to the correct value so the column rID would look like this:

rN      rID   rnk        dateR        value1 value2 calculation1 calculation2 calculation3
abc_3m   3   abc_3mxfx  20.10.2010     1       3      4            3             0.33
abc_6m   4   abc_6mxfx  20.10.2010     2       1      3            8             4
abc_1y   5   abc_6mxfx  20.10.2010     2       1      3            8             4
abc_2y   6   abc_6mxfx  20.10.2010     2       1      3            8             4

In order to do so, I was thinking of writing something like this:

query 2

update #myFirstTable
set rID =  (case when rnk = 'abc_1y' then rID = '100', 
            case when rnk = 'abc_2y' then rID = '101'
            case when rnk = 'abc_3y' then rID = '102'
            case when rnk = 'abc_4y' then rID = '103'
            case when rnk = 'abc_5y' then rID = '104'
            case when rnk = 'abc_7y' then rID = '105'
            case when rnk = 'abc_10y' then rID = '106'
            case when rnk = 'abc_15y' then rID = '107'
            case when rnk = 'abc_30y' then rID = '108'
            ) end 
 

select * from #myFirstTable
where rN = 'abc_6M'

Questions: 1. Is my logic correct for what I'm trying to achieve? (are query1 and query2 ok syntax wise? Can I write something like this in sql server 2012?

2. Is it ok to write an update column = (case when column = then another column = 'value' like that ?

3. Could I do this in a simpler way somehow using a window function?

ex:

select * from myTable 
case (
when rN not in ('abc_1m', 'abc_3m', 'abc_6m') then 
select * from #myFirstTable where rN like 'abc_6m' 
over (partition by (select * from #myFirstTable where rN like 'abc_6m'))) end 

Sorry if I'm not phrasing the question in the most clear way, but I am not sure what I could use and how to use it, that's why I'm looking for some guidence. Thanks

Answer 1

EDIT:

I'm only a beginner and this is the best I could come up with, which seems to be what you want to do. Following steps are building up on the already existing table with the two rows called MyFirstTable.

1. step (inserting additional values in the rN column):

INSERT INTO MyFirstTable (rN)
VALUES ('abc_1y'),('abc_2y'),('abc_3y'),('abc_4y'),('abc_5y'),('abc_7y'),('abc_10y'),('abc_15y'),('abc_30y');

2. step (dropping rID column and creating a new one with auto_incremeneted values)

ALTER TABLE MyFirstTable
DROP COLUMN rID;

ALTER TABLE MyFirstTable
ADD rID int IDENTITY(3,1);

More info: http://www.w3schools.com/sql/sql_autoincrement.asp

3. step (updating fields in other columns with values from the 2nd row)

Here, the update is combined with something called "self-join", because the FROM clause doesn't allow us to use the same source table as the one that is being updated and so nested query to retrieve the desired value cannot be used.

UPDATE t1
SET 
    t1.rnk = t2.rnk,
    t1.dateR = t2.dateR,
    t1.value1 = t2.value1,
    t1.value2 = t2.value2,
    t1.calculation1 = t2.calculation1,
    t1.calculation2 = t2.calculation2,
    t1.calculation3 = t2.calculation3
FROM MyFirstTable t1
LEFT JOIN MyFirstTable t2 ON t2.rN='abc_6m'
WHERE t1.rN in ('abc_1y','abc_2y','abc_3y','abc_4y','abc_5y','abc_7y','abc_10y','abc_15y','abc_30y');

More info on self-join: https://technet.microsoft.com/en-us/library/ms177490%28v=sql.105%29.aspx

I found this answer to be helpful: https://stackoverflow.com/a/5574558/4296411

Warning: I'm really only a beginner and possibly none of the above may be considered a good practice and might contain mistakes leading to unpredictable results. :) It nevertheless seems to be answering your problem. Good luck!

EDIT2:

Well, this is most probably a ridiculous overkill, but since you cannot drop the rID column, I was able to come up with this rather complex solution. First, you would perform step #1, then the step#3 and then:

DECLARE @i INT = 1;
WHILE @i <= (SELECT COUNT(rN) FROM MyFirstTable WHERE rN IN ('abc_1y','abc_2y','abc_3y','abc_4y','abc_5y','abc_7y','abc_10y','abc_15y','abc_30y')) -- getting number of desired loops based on rows to be affected
BEGIN
   UPDATE MyFirstTable
   SET rID = 4+@i -- using number 4 as a starting point for incrementation and @i variable to perform the actual incrementation
   WHERE 
        rN = ( -- specifying which row should be affected in each loop
            SELECT SUBQUERY.rN
            FROM ( -- assigning temporary row number for each desired row
                SELECT ROW_NUMBER() OVER (ORDER BY value1) as row_num, rN -- using value1 in order to keep the current order (this doesn't seem to be a good practice, but it works)
                FROM MyFirstTable
                WHERE rN IN ('abc_1y','abc_2y','abc_3y','abc_4y','abc_5y','abc_7y','abc_10y','abc_15y','abc_30y')
                ) as SUBQUERY
            WHERE SUBQUERY.row_num = @i
            )
   SET @i = @i + 1;
END;

PS: Please don't worry about my slight obsession with your problem. :D It's just that I consider this problem fun and It helps me to educate myself further.