help with mysql transaction

[kage]

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

[techstress]

i'd prolly write a cursor function that iterates through each row in table1.

using that row, run a query to the find the max value for XX in table 2

if the xx is larger in table 1, execute update query.




something like this

USE AdventureWorks2008R2;
GO
SET NOCOUNT ON;

DECLARE @id int, @xx int

DECLARE vendor_cursor CURSOR FOR 
SELECT id, xx from table1

OPEN vendor_cursor;

FETCH NEXT FROM vendor_cursor 
INTO @id, @xx;

WHILE @@FETCH_STATUS = 0
BEGIN

    if ((select max(xx) from table2 where id=@id) < @xx)
        insert into table2 ('id', 'xx') values (@id, @xx)

    FETCH NEXT FROM vendor_cursor 
    INTO @id, @xx;
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

[kgore4]

how about

insert into table2
select *
from table1
where xx > (select max(xx) from table2 where table2.id = table1.id);

[kage]

how about

insert into table2
select *
from table1
where xx > (select max(xx) from table2 where table2.id = table1.id);

i should have replied when i solved this, kgore my solution was almost identical to yours... thanks for the replies


theres one last thing i need to do, i think i have a solution but i wont be able to sit down and write it until tomorrow so in the mean time if someone knows a better solution off the top of their heads let me know

what i need to do next is take table 2 and sum the last 5 XX for each id and insert it into table 3

example
Table2
[id][xx][y]
[1 ][1][1]
[1] [2][9]
[1] [3][2]
[1] [4][3]
[1] [5][0]
[2] [1][10]
[2] [2][4]

we see for id=1 XX max = 5 so i need to sum(y) for XX > (max(XX) - 5) , and the same for id=2, id = ...
essentially inserting:
[id][y]
[1][15]
[2][14]

into table 3