Find the answer to your Linux question:
Results 1 to 4 of 4
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 ...
Enjoy an ad free experience by logging in. Not a member yet? Register.
  1. #1
    Just Joined!
    Join Date
    Mar 2005
    Posts
    8

    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

    |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

  2. #2
    Just Joined!
    Join Date
    Oct 2010
    Posts
    14
    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

    Code:
    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;

  3. #3
    Just Joined!
    Join Date
    Jul 2010
    Posts
    4
    how about

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

  4. #4
    Just Joined!
    Join Date
    Mar 2005
    Posts
    8
    Quote Originally Posted by kgore4 View Post
    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

Posting Permissions

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