• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

MySQL complex update

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
0
rkeith2412
Asked:
rkeith2412
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
0
 
rkeith2412Author Commented:
I am looking to update, I already have an insert using the same field in the users table.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
rkeith2412Author 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
0
 
rkeith2412Author Commented:
I was really close, thanks for the help.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I put a syntax suggestion above also, which should help also.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now