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

ameyer · #1 (**permalink**) 12-06-09, 17:25

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

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.

ameyer · #2 (**permalink**) 12-07-09, 00:15

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.

mike_bike_kite · #3 (**permalink**) 12-07-09, 04:55

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

ameyer · #4 (**permalink**) 12-07-09, 08:16

We have a winner!

Thank you!!!!!!!!

mike_bike_kite · #5 (**permalink**) 12-07-09, 08:28

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

ameyer · #6 (**permalink**) 12-07-09, 08:50

That's very nice of you.

Normally I do ask for free help.
Thanks again,

-Adam

Quote:

Originally Posted by mike_bike_kite

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

Internet Services

Web Development

Digital Marketing

Consumer Tech