Wherever CODE="BB", i wish to place a brand new row with the same row/column data just alter the CODE to "XX".
ID | CODE | Name ---+----------+--------- 1 | AA | Tom 2 | BB | Mary 3 | AA | John 4 | CC | Carlos 5 | CC | Mario 6 | BB | Steve 7 | AA | Pablo
Same After SQL statement performed:
ID | CODE | Name ---+----------+--------- 1 | AA | Tom 2 | BB | Mary 3 | AA | John 4 | CC | Carlos 5 | CC | Mario 6 | BB | Steve 7 | AA | Pablo 8 | XX | Mary 9 | XX | Steve
Is this completed in a SQL statement? instead of do things i would do that was produce a script to loop through each record checking for "BB" and placing a brand new row according to it's values.
This code features a check to make certain an 'XX' version does not already exist.
INSERT INTO CurrentTable (CODE, Name) SELECT 'XX', ct1.Name FROM CurrentTable ct1 WHERE ct1.CODE = 'BB' AND NOT EXISTS(SELECT NULL FROM CurrentTable ct2 WHERE ct1.name = ct2.name AND ct2.CODE = 'XX')
Presuming ID is definitely an IDENTITY column:
INSERT INTO myTable (Code, Name) SELECT ('XX', Name) FROM myTable WHERE CODE ='BB'
I'm not sure the way you generate id's but this is the way it's working:
insert into my_table select new_id() as id, 'xx' as Code, Name from my_table where Code='BB'