change value in table

[thompom]

Hi,

Have 10 applications with individual user tables.
I want to consolidate all applications with one user table.
[i know - should have done this from start]

My issue is the old user id is stored in the primary table and i need to replace with the new user id.

To do this i have added a new column to the old user table called newuserid and filled it with the newuserid's.
now i want to replace the old userid with the new userid in the primary table.

have tried with the following - am i on the right track ?

UPDATE primarytable
SET userid = newuserid 
FROM users
WHERE users.userid = primarytable.userid

 

[ingresman]

Have you tried it ?
what happend ?

 

[thompom]

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM users

 

[johnwm]

The UPDATE statement doesn't take a FROM argument. See

http://dev.mysql.com/doc/refman/5.1/en/update.html

Looks like you want to replace ALL the userid values with the corrsponding newuserid values, so no WHERE clause is needed

UPDATE primarytable
SET userid = newuserid

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[thompom]

hi john thanks for your reply - the link helped alot

the following did what i needed

UPDATE primarytable,users SET primarytable.userid=users.newuserid
WHERE primarytable.userid=users.userid;

 

[r937]

FYI you can also use JOIN syntax

UPDATE primarytable
INNER
JOIN users
ON primarytable.userid=users.userid
SET primarytable.userid=users.newuserid


r937.com | rudy.ca