Find the answer to your Linux question:
Results 1 to 4 of 4
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1

    help with mysql transaction

    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

    |1 |1 |
    |2 |5 |
    |3 |3 |

    table 2 has something like this:
    |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

  2. #2
    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;
    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;
        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;
    CLOSE vendor_cursor;
    DEALLOCATE vendor_cursor;

  3. #3
    how about

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

  4. $spacer_open
  5. #4
    Quote Originally Posted by kgore4 View Post
    how about

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

    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

    [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:

    into table 3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts