help with more complicated sql transaction
Posted: Thu Oct 07, 2010 9:57 am
Im having some difficulty with a certain transaction and i am kinda working on a timeframe so im asking for help from sql gurus.
heres the situation:
i have two tables which are essentially the same in structure, lets call them table1 and table2, the only difference is that the id is not unique on table 2.
lets pretend theres this data in table1
|ID|XX|
|1 |1 |
|2 |5 |
|3 |3 |
table 2 has something like this:
|ID|XX|
|1 |1 |
|2 |4 |
|2 |3 |
|2 |2 |
|3 |3 |
|3 |2 |
i am trying to copy rows FROM table1 TO table2 where the the table1.XX is greater then the max XX in table2 grouped by the ID
for the above table, the record |2 | 5 | would be copied over because |2 |4 | is the highest in table2, so that means for ID=2 MAX(XX)=4 in table2 but for ID=2 XX=5 in table1, so copy that element to table2
i hope i have been clear enough, and appreciate the help
heres the situation:
i have two tables which are essentially the same in structure, lets call them table1 and table2, the only difference is that the id is not unique on table 2.
lets pretend theres this data in table1
|ID|XX|
|1 |1 |
|2 |5 |
|3 |3 |
table 2 has something like this:
|ID|XX|
|1 |1 |
|2 |4 |
|2 |3 |
|2 |2 |
|3 |3 |
|3 |2 |
i am trying to copy rows FROM table1 TO table2 where the the table1.XX is greater then the max XX in table2 grouped by the ID
for the above table, the record |2 | 5 | would be copied over because |2 |4 | is the highest in table2, so that means for ID=2 MAX(XX)=4 in table2 but for ID=2 XX=5 in table1, so copy that element to table2
i hope i have been clear enough, and appreciate the help