Solved

MySQL; update on two tables

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
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 …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now