Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    32

    Arrow Unanswered: $20 via paypal if you can solve this. (probably easy)

    ************************************************** ******************************
    ************************************************** ******************************

    SOLVED!

    ************************************************** ******************************
    ************************************************** ******************************

    I will paypal you $20 within the day (hour if Im up) if you can solve this.

    I have a table called file_versions - he it setup like so

    Code:
    version_id	file_id		session_id 		parent
    
    1		1		1			0
    5		2		1			0
    20		13		1			5
    21		20		1			5
    25		6		1			1
    I then duplicate this session 1 like so:

    Code:
    INSERT INTO file_versions
    	    ( session_id   
    	    , file_id
    	    , parent )
    	SELECT 
    	    ' 2
    	    , file_id
    	    , parent
    	  FROM file_versions
    	 WHERE session_id = 1
    So I now have

    Code:
    version_id	file_id		session_id 		parent
    
    1		1		1			0
    5		2		1			0
    20		13		1			5
    21		20		1			5
    25		6		1			1
    
    
    26		1		2			0
    27		2		2			0
    28		13		2			5
    29		20		2			5
    30		6		2			1
    So now the question. I need to change the parents of session 2 so they are relational to their version_id (except those who have a parent of 0)

    so it should look like this if it worked:
    Code:
    version_id	file_id		session_id 		parent
    
    1		1		1			0
    5		2		1			0
    20		13		1			5
    21		20		1			5
    25		6		1			1
    
    
    26		1		2			0
    27		2		2			0
    28		13		2			27
    29		20		2			27
    30		6		2			26

    I had this working a while back, but something happened.... Have no clue what. I may have modified it without noticing at some point. (this looks wrong to me now because there is no way to know what you are grabbing)

    Code:
    UPDATE file_versions 
    	SET parent = parent + ( LAST_INSERT_ID( ) - ( SELECT version_id
    		FROM (
    	
    			SELECT * 
    			FROM file_versions
    			WHERE session_id = 1
    			ORDER BY version_id ASC 
    			LIMIT 1
    		) AS x ) ) 
    	WHERE session_id = 2
    	AND parent !=0';
    make it $35 if you can do this in one query. But im not 100% that is even possible.
    Last edited by ameyer; 12-07-09 at 09:18.

  2. #2
    Join Date
    Jul 2009
    Posts
    32
    So this is absolutely crazy, but I have something that might help.

    I was assuming that I could use the version_id to parent_id relationship as a means to do this. But that is wrong (it is not a linear move all the time (IE it is not always 20 highe for all of them)). I need to use the file_id. Because the file_ids copy over exactly the same, and are unique to a session, I can use that.

    For instance, this is how I could do one of them.
    Keep in mind, part of this is to get around the mySQL limitations, others because I really have no clue what I am doing here.


    Code:
    UPDATE file_versions SET file_versions.parent = (
    SELECT x.version_id
    FROM (
    	SELECT file_versions.version_id
    		FROM file_versions
    		WHERE file_versions.file_id = ( 
    			SELECT file_id
    			FROM file_versions
    			WHERE file_id = ( 
    				SELECT file_id
    				FROM file_versions
    				WHERE version_id = ( 
    					SELECT parent
    					FROM file_versions
    					WHERE version_id =28
    				)
    			) 
    			AND session_id =1
    		) 
    		AND session_id =2
    	)AS x
    )
    WHERE file_versions.version_id =28

    So this does this to one particular line. How do I do this to all of the session 2 guys whose parents are != 0


    This might help more:
    Im looking at the parent (copied from the older session,) going over to the old session, and looking at that record, checking what the file_number is, then looking for that file number in the new session, when found, looking to see what the version_id is for it, then setting the parent equal to that.

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    It ain't pretty but I believe it should work for you:
    Code:
    create temporary table if not exists tmp_ids(
       child_id int,
       parent_id int );
    
    insert tmp_ids
    select distinct a.version_id, d.version_id
    from file_versions a,file_versions b,file_versions c,file_versions d
    where a.session_id=2
    and a.parent != 0
    and b.session_id=1
    and b.file_id = a.file_id
    and c.version_id=b.parent
    and c.session_id=1
    and d.file_id=c.file_id
    and d.session_id=2;
    
    update file_versions
    set parent = ( select t.parent_id from tmp_ids t where t.child_id=file_versions.version_id )
    where session_id=2
    and parent!=0;
    One statement is not possible as MySQL objects if you update the table you're selecting from. Of course if you only have 8 records of data then you could just insert it all by hand

    Mike

  4. #4
    Join Date
    Jul 2009
    Posts
    32
    We have a winner!

    Thank you!!!!!!!!

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    As a moderator I should point out you can (and should) be getting your answers for free like everyone else but seeing as it's Christmas I'll put your donation towards the kids presents

  6. #6
    Join Date
    Jul 2009
    Posts
    32
    That's very nice of you.

    Normally I do ask for free help.
    Thanks again,

    -Adam



    Quote Originally Posted by mike_bike_kite View Post
    As a moderator I should point out you can (and should) be getting your answers for free like everyone else but seeing as it's Christmas I'll put your donation towards the kids presents

Posting Permissions

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