MySQL complex update

rkeith2412
rkeith2412 used Ask the Experts™
on
Ok, let's see if I can expalin what I want.  Basically I have three tables called record, records, and users.  I am migrating data from record to records which is layed out a little better with less data being duplicated.

I need to read users.id where record.supEmpNum = users.empNum and update records.supID with the returned data where record.id = records.id
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
is this a UPDATE or a INSERT?

assuming it's a UPDATE, consider reading this article that explains, though not for MySQL alone, how to build up the UPDATES with JOINS:
http://www.experts-exchange.com/articles/Database/Miscellaneous/UPDATES-with-JOIN-for-everybody.html

Author

Commented:
I am looking to update, I already have an insert using the same field in the users table.
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
so, did you read the article?

apart from that, here is the MySQL syntax doc:
http://dev.mysql.com/doc/refman/5.0/en/update.html

UPDATE users u, records s, record r 
  SET s.supID = u.ID
WHERE u.empNum = r.subEmpNum
  AND r.id = s.id

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
yes I did, I had moved on to the syntax of the update statement at http://dev.mysql.com/doc/refman/5.0/en/update.html

Author

Commented:
I was really close, thanks for the help.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
I put a syntax suggestion above also, which should help also.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial