|
View previous topic :: View next topic
|
| Author |
Message |
Daphne
New User
Joined: 28 Aug 2006
Posts: 27
Location: NY, US
|
|
|
|
Hi all,
I have a table <table1> with columns <prev year>, <col2>, <col3> , <2>.
I have thousands of rows in this table.
Every start of the year I have to change the previous year into current year (no update) and change <2> to <3> and insert the row in the same table.
Once this is done we will have two rows
<prev year>, <col2>, <col3>, <2>
<current year>, <col2>, <col3>, <3>
I am intending to use mass insert using select, but not sure how to put the constants in between (year and 3).
Appreciate any help.
Thanks,
Daphne |
|
| Back to top |
|
 |
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
Daphne, Is this what you are looking to happen?
| Code: |
DECLARE GLOBAL TEMPORARY TABLE TEMP_TBL
(
C_YEAR DEC(4),
COL2 DEC(3),
COL3 CHAR(1),
INCR DEC(3)
)
;
SELECT *
FROM SESSION.TEMP_TBL
;
INSERT
INTO SESSION.TEMP_TBL
(C_YEAR,
COL2,
COL3,
INCR)
SELECT 2007,
COL2,
COL3,
3
FROM SESSION.TEMP_TBL
WHERE C_YEAR = 2006
AND INCR = 2
;
SELECT *
FROM SESSION.TEMP_TBL
;
|
First SELECT*
| Code: |
+----------------------------------+
| C_YEAR | COL2 | COL3 | INCR |
+----------------------------------+
1_| 2005 | 15 | A | 1 |
2_| 2005 | 17 | B | 2 |
3_| 2006 | 15 | A | 2 |
4_| 2006 | 17 | B | 2 |
+----------------------------------+
|
Second SELECT*
| Code: |
+----------------------------------+
| C_YEAR | COL2 | COL3 | INCR |
+----------------------------------+
1_| 2005 | 15 | A | 1 |
2_| 2005 | 17 | B | 2 |
3_| 2006 | 15 | A | 2 |
4_| 2006 | 17 | B | 2 |
5_| 2007 | 15 | A | 3 |
6_| 2007 | 17 | B | 3 |
+----------------------------------+
|
|
|
| Back to top |
|
|
Daphne
New User
Joined: 28 Aug 2006
Posts: 27
Location: NY, US
|
|
|
|
Hi Dave,
Thanks for your explanative reply. Yes this is what I was looking for.
I am using embedded SQL. I was trying different ways and at last got into the same solution(well...almost :-) ).
INSERT INTO <tablename>
SELECT '2007'
, A.col2
, A.col3
, '3'
FROM <tablename> A
WHERE A.YEAR = '2006'
AND A.col4 = '2'
Thanks again,
Daphne |
|
| Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
Well, I'm glad we came up with similar solutions. They validate each other 
Dave |
|
| Back to top |
|
|
|
|
|
