Help writing a simple SQL query to update uid.
I'm going to use the user import module to import several users from a different system. For reasons the UID of each user has to remain the same in the new site as in the old. The user ID is used for other purposes and there are hundreds of laminated cards with each users costumer ID out there.
I already tested the user import module and it works great except I can't find a way to map the unique costumer number to the new site's UID. My plan is to import the costumer ID by creating a field in the profile table called costumer ID and map it to the costumer number of the old database. After everything is finished I will manually write a SQL query and execute it in phpMyAdmin.
Right now there is only user 1 in the database (and some test users which will be erased). I want to then change each UID to the customer ID. I opened phpMyAdmin and changed one of my test user's uid. This is the query phpMyAdmin came up with.
UPDATE `horton79_a`.`users` SET `uid` = '14' WHERE `users`.`uid` =20 LIMIT 1 ;Within the database that is being imported both the email address and customer ID are unique. I don't know how to use that to my advantage, but I do know it is a good thing. I'm going to start by trying to write a query, but I don't know anything about SQL. The fid is the field id of the custumer ID that is being imported by the user import module. This is my first attempt at writing an sql query, ever, so I'm just guessing what it might look like. What do you think? And, how do I make it work?
UPDATE 'horton79_a'.'users' SET 'uid' = 'profile_values'.'value' WHERE ('users'.'uid' = 'profile_values'.'uid' AND 'profile_values'.'fid' = 26);
Use this query UPDATE users
Use this query
UPDATE users INNER JOIN profile_values on users.uid = profile_values.uid AND profile_values.fid = 10 SET users.uid = profile_values.valueit didn't work. Here is the
it didn't work. Here is the error.
SQL query:
UPDATE users INNER JOIN profile_values ON users.uid = profile_values.uid AND profile_values.fid =32 SET users.uid = profile_values.value
MySQL said: Documentation
#1062 - Duplicate entry '96' for key 1
http://thetriton.com
Your query does work.
Your query does work. However, I have to make sure that all the imported users have high uid than the number of the customer ID or else there are during the process two different users with the same ID while the process sorts it out.
http://thetriton.com
The issue now is updating the
The issue now is updating the UID in the profile_values table. Every value for uid that is 55802 needs to change to 93 the value of the row with fid = 32.
SQL result
Host: localhost
Database: horton79_a
Generation Time: Oct 22, 2009 at 03:10 AM
Generated by: phpMyAdmin 2.11.9.5 / MySQL 5.0.81-community
SQL query: SELECT `fid`, `uid`, `value` FROM `profile_values` LIMIT 0, 30 ;
Rows: 30
fid uid value
14 55802 USA
16 55802 1-954-7
22 55802 http:/
32 55802 93
33 55802 company
4 55803 Bob
5 55803 D
6 55803 No match
14 55803 USA
16 55803 1-954-76
25 55803 Peterso
32 55803 96
33 55803 company
4 55804 John
5 55804 Ve
6 55804 business management
14 55804 USA
16 55804 1-954-
17 55804 954
22 55804 http://www
25 55804 Super
32 55804 99
33 55804 company
4 55805 Deas
5 55805 Manning
6 55805 Marketing
14 55805 USA
16 55805 1-843-
22 55805 www.
http://thetriton.com
Ok, now I made a new table in
Ok, now I made a new table in the database called profile_switch which I'm hoping can take the first UPDATE query and change the profile_values table with it. I created the table in phpMyAdmin. Here is the query:
INSERT INTO profile_switch( uid, switch )SELECT uid, value
FROM profile_values
WHERE fid =32
Here is the query to make the switch
UPDATE profile_values INNER JOIN profile_switch ON profile_values.uid = profile_switch.uid SET profile_values.uid = profile_switch.switchBAM!!!! It worked....... I can't even tell you how high I am right now.
http://thetriton.com