mysql inserting static index values to existing index column
we had the base question posted earlier today and have found a solution however our results are not exactlty what we need,
we have a table with a primary key index set by auto-increment and other data and also an index key that is a foriegn key of a primary key in another table. Instead of manually editing each index key with the matchng PK in another table we developed a query to populate the index key with correct value which is the PK of another table. The problem is that the query runs and inserts the correct values and in the correct order to match (at sequencially) the PK in the other table however, the insertion starts at the last row in the table and all of the actual records that we need to change remain as their default value, which 0. we tried several different statements such as insert ignore, replace into, insert into but all yeild the same result. below is the basic query we used to populate the foriegn keys.
Im sure we have the wrong statement somewhere but where.
please help.
replace into members (player_PKID)
(SELECT
players.player_PKID
FROM
players
INNER JOIN members
ON players.last_name = members.last_name
WHERE
members.last_name = players.last_name)
we have a table with a primary key index set by auto-increment and other data and also an index key that is a foriegn key of a primary key in another table. Instead of manually editing each index key with the matchng PK in another table we developed a query to populate the index key with correct value which is the PK of another table. The problem is that the query runs and inserts the correct values and in the correct order to match (at sequencially) the PK in the other table however, the insertion starts at the last row in the table and all of the actual records that we need to change remain as their default value, which 0. we tried several different statements such as insert ignore, replace into, insert into but all yeild the same result. below is the basic query we used to populate the foriegn keys.
Im sure we have the wrong statement somewhere but where.
please help.
replace into members (player_PKID)
(SELECT
players.player_PKID
FROM
players
INNER JOIN members
ON players.last_name = members.last_name
WHERE
members.last_name = players.last_name)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Very nice indeed, works perfect and will fit into almost all of our dbase prep for production.
Thanks very much for this, and thanks for the class today professor.
Sometimes the simple solution is the best solution.
Ron
Thanks very much for this, and thanks for the class today professor.
Sometimes the simple solution is the best solution.
Ron
ASKER
After researching for an hour or more and trying many solutions that we compiled, mwvisa1 stepped up and was right on target with the solution he provided, perfect out of the box. He is one of the reasons we joined EE and continue to be a member. It is alos time we started giving back to the EE community so we will be moving to provide solutions where we can. Thanks mwvisa1!
*smile* You are welcome. Yes, keeping it simple avoids a lot of headaches. The error you noted is indeed well known and so avoiding complicated scenarios that require it is the best remedy.
Best regards and happy coding,
Kevin
Best regards and happy coding,
Kevin
ASKER
1 You can't specify target table 'members' for update in FROM clause
research indicates this is a big problem with MYSQL. I have found several work arounds, most of them use alias tables to fool MYSQL but I havent been able to get the syntax right as it would apply to my query as shown below. Also I can hard code the set player_PKID= to a value like "5' and the query doesnt show the error but hardcoding a value here wont solve the original problem as I need to join the two table together and then get the index keys and update them into the appropriate fields where they match existing PK indexes.
UPDATE members set player_PKID=
(SELECT
players.player_PKID
FROM
players
INNER JOIN members
ON players.last_name = members.last_name
WHERE
members.player_PKID = 0) //probably not needed it was just an attempt to isolate the index field to be updated
Come on experts, any takers?, I need a bit of help.