At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
MySQL; update on two tables
Posted on 2014-11-12
I need to replace the c.EXTRA_ID in the ccode table with the e.EXTRA_ID in the extra table.
extra
+-------------------------
| EXTRA_ID | JOB_ID | JobID | ExtraID |
| 1000953 | 55418 | 12-15-0001 | 801 |
ccode
+-------------------------
| CCODE_ID | JOB_ID | EXTRA_ID | SCCODE_ID | JobID | ExtraID |
| 803064 | 55418 | 680999 | 36 | 12-15-0001 | 801 |
UPDATE ccode c, extra e
SET c.EXTRA_ID = e.EXTRA_ID
WHERE e.JOB_ID = c.JOB_ID AND e.ExtraID = c.ExtraID AND c.EXTRA_ID = 1000954;
When I run it it say it was successful, but it doesn't update c.EXTRA_ID
Query: update ccode c, extra e set c.EXTRA_ID = e.EXTRA_ID where e.JOB_ID = c.JOB_ID and e.ExtraID = c.ExtraID and c.EXTRA_ID = 1000953
0 row(s) affected
extra
+-------------------------
| EXTRA_ID | JOB_ID | JobID | ExtraID |
| 1000953 | 55418 | 12-15-0001 | 801 |
ccode
+-------------------------
| CCODE_ID | JOB_ID | EXTRA_ID | SCCODE_ID | JobID | ExtraID |
| 803064 | 55418 | 680999 | 36 | 12-15-0001 | 801 |
UPDATE ccode c, extra e
SET c.EXTRA_ID = e.EXTRA_ID
WHERE e.JOB_ID = c.JOB_ID AND e.ExtraID = c.ExtraID AND c.EXTRA_ID = 1000954;
When I run it it say it was successful, but it doesn't update c.EXTRA_ID
Query: update ccode c, extra e set c.EXTRA_ID = e.EXTRA_ID where e.JOB_ID = c.JOB_ID and e.ExtraID = c.ExtraID and c.EXTRA_ID = 1000953
0 row(s) affected
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
2
4 Comments
Try this :
I assume the Extra_id is from Extra table so you miss typed the table synonim
update ccode c, extra e set c.EXTRA_ID = e.EXTRA_ID where e.JOB_ID = c.JOB_ID and e.ExtraID = c.ExtraID and e.EXTRA_ID = 1000953
I assume the Extra_id is from Extra table so you miss typed the table synonim
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month4 days, 3 hours left to enroll
630 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.