Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.
Course Of The Month
2 days, 12 hours left to enrollBecome 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
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| How can i make performance tuning to my sql query? | 6 | 70 | |
| restriction of entering a a page | 5 | 44 | |
| MySQL - passing table names to a stored procedure | 3 | 50 | |
| No option to replace table data in phpmyadmin using CSV file import | 11 | 52 |
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
- Databases
- AWS
- MySQL Server
- MongoDB
- NoSQL Databases
- Percona
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers.
According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Suggested Courses
Course of the Month2 days, 12 hours left to enroll
752 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.