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)
SET parent = parent + ( LAST_INSERT_ID( ) - ( SELECT version_id
WHERE session_id = 1
ORDER BY version_id ASC
) 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.
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.
UPDATE file_versions SET file_versions.parent = (
WHERE file_versions.file_id = (
WHERE file_id = (
WHERE version_id = (
WHERE version_id =28
AND session_id =1
AND session_id =2
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.
It ain't pretty but I believe it should work for you:
create temporary table if not exists tmp_ids(
parent_id int );
select distinct a.version_id, d.version_id
from file_versions a,file_versions b,file_versions c,file_versions d
and a.parent != 0
and b.file_id = a.file_id
set parent = ( select t.parent_id from tmp_ids t where t.child_id=file_versions.version_id )
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