Solved

MySQL; update on two tables

Posted on 2014-11-12
4
161 Views
Last Modified: 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
0
Comment
Question by:hdcowboyaz
  • 2
4 Comments
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 500 total points
ID: 40438819
Try this :

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

Open in new window


I assume the Extra_id is from Extra table so you miss typed the table synonim
0
 

Author Comment

by:hdcowboyaz
ID: 40438820
never mind, brain freeze. should be

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 = 1000954;
0
 

Author Closing Comment

by:hdcowboyaz
ID: 40438822
Yes, thaniks
0
 

Expert Comment

by:Vsevolod Geraskin
ID: 40438823
replace c.EXTRA_ID = 1000954; with e.EXTRA_ID = 1000954; ?
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question