Solved

MySQL; update on two tables

Posted on 2014-11-12
4
160 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
phpMyAdmin simple sql statement 3 58
MySQL query to show different levels from a table. 5 65
count download link and run update query 9 70
category table 2 24
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
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 …

785 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