?
Solved

UPDATE one table based on JOIN of two tables.  MySQL5

Posted on 2007-09-28
6
Medium Priority
?
1,042 Views
Last Modified: 2008-01-09
I want to update a field called "fab_code" in one table called "sharkey" based on a join of two tables called "sharkey" and "fee_programs."  The below query runs but doesn't make any changes!  Could someone help me with this?

UPDATE sharkey s, fee_programs f SET s.fab_code=f.fab_code WHERE s.fee_id=f.fee_id

http://dev.mysql.com/doc/refman/5.0/en/update.html
http://www.t1shopper.com
0
Comment
Question by:Geoff Millikan
  • 3
  • 3
6 Comments
 
LVL 12

Expert Comment

by:AdrianSRU
ID: 19980716
The query looks correct.  Try running the query as a select statement:

SELECT s.fab_code, f.fab_code FROM sharkey s, fee_programs f WHERE s.fee_id=f.fee_id;

If there are no records returned that have different values for s.fab_code and f.fab_code then there is nothing for the statement to update.


--Adrian
0
 

Author Comment

by:Geoff Millikan
ID: 19980786
Ran the query:
SELECT s.fab_code, f.fab_code FROM sharkey s, fee_programs f WHERE s.fee_id=f.fee_id limit 100;

And it returned:
NULL, 'M78DA00'
NULL, 'M75CE00'
'NULL, 'M75CE00'
NULL, 'M75CE00'
...
0
 
LVL 12

Expert Comment

by:AdrianSRU
ID: 19980921
That doesn't make sense at all!  Please post the CREATE TABLE statements for both tables as well as a small amount of data from each table that should be joined together for the UPDATE.


--Adrian
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:Geoff Millikan
ID: 19981064
CREATE TABLE `sharkey` (
  `sharkey_id` int(10) unsigned NOT NULL auto_increment,
  `ARN` varchar(23) NOT NULL,
  `trans_seq_nbr` int(12) unsigned NOT NULL,
  `lt_ed_refnum` char(8) default NULL,
  `logtran_transaction_id` int(10) unsigned default NULL,
  `TID` char(12) NOT NULL,
  `CC_FIRST6` char(6) NOT NULL,
  `CC_LAST4` char(4) NOT NULL,
  `credit_debit_card_flag` char(1) default NULL,
  `iss_cntry_code_a2` varchar(2) default NULL,
  `iss_cntry_code_a3` varchar(3) default NULL,
  `approval_code` char(6) default NULL,
  `trans_dttm` datetime default NULL,
  `activity_dttm` datetime default NULL,
  `card_scheme` char(2) default NULL,
  `echo_id` char(10) default NULL,
  `amt_trans` decimal(8,2) default NULL,
  `mas_code` varchar(17) default NULL,
  `trans_id` varchar(15) default NULL,
  `processing_code` char(6) default NULL,
  `pos_entry_mode` char(2) default NULL,
  `pos_environment` char(1) default NULL
  `avs_result` char(1) default NULL,
  `mote_e_com_ind` char(2) default NULL,
  `ACI` char(1) default NULL,
  `RPS` char(1) default NULL,
  `cardholder_ID_method` char(1) default NULL,
  `RA` char(1) default NULL,
  `MS_Data_Indicator` char(1) default NULL,
  `validation_code` char(4) default NULL,
  `FPI` char(3) default NULL,
  `IRD` char(2) default NULL,
  `GCMS_product_identifier` char(3) default NULL,
  `licensed_product_identifier` char(3) default NULL,
  `business_service_type` varchar(2) default NULL,
  `auth_trace_id` varchar(15) default NULL,
  `auth_dttm` char(10) default NULL
  `auth_stan` varchar(45) default NULL
  `fee_id` mediumint(8) unsigned default NULL,
  `fab_code` varchar(10) default NULL,
  `interchange_rate` decimal(5,4) unsigned default NULL,
  `interchange_unit_fee` decimal(4,2) unsigned default NULL,
  `interchange_cost` decimal(7,2) default NULL,
  `merchant_xp_fee` decimal(8,4) default NULL,
  `merchant_discount_fee` decimal(2,2) unsigned default NULL,
  `merchant_discount_rate` decimal(5,4) unsigned default NULL,
  `merchant_cost` decimal(9,4) default NULL,
  `xp_level` varchar(2) default NULL,
  `profit_loss` decimal(9,4) default NULL,
  `error_flag1` char(1) default NULL,
  `card_program_flag` varchar(1) default NULL,
  `comm_card_flag` varchar(1) default NULL,
  `MCC` char(4) default NULL,
  `card_level_results` varchar(2) default NULL,
  `merchant_group` char(1) default NULL,
  PRIMARY KEY  (`sharkey_id`),
  UNIQUE KEY `Require_Unique_ARN` USING BTREE (`ARN`),
  KEY `LogTran_Ref_Num` (`lt_ed_refnum`),
  KEY `merchant_group` (`merchant_group`),
  KEY `echo_id` USING BTREE (`echo_id`)
) ENGINE=InnoDB AUTO_INCREMENT=24136519 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='InnoDB free: 2894848 kB; InnoDB free: 3686400 kB'


CREATE TABLE `fee_programs` (
  `fee_id` mediumint(8) unsigned NOT NULL auto_increment
  `card_assn` varchar(10) NOT NULL default ''
  `visa_fpi` varchar(3) default ''
  `visa_fee_descriptor` varchar(85) default ''
  `mastercard_ird` varchar(3) default ''
  `fee_program` varchar(85) default ''
  `fee_item` decimal(4,2) unsigned zerofill default '00.00'
  `fee_max` decimal(3,2) unsigned zerofill default '0.00'
  `fee_min` decimal(3,2) unsigned zerofill default '0.00'
  `fee_currency` char(3) default ''
  `credit_debit_card_flag` varchar(45) default ''
  `card_products` varchar(40) default NULL,
  `region` varchar(45) default ''
  `card_notes` text
  `modified_dttm` timestamp NOT NULL default '0000-00-00 00:00:00',
  `insert_dttm` datetime NOT NULL default '0000-00-00 00:00:00',
  `effective_dttm` datetime NOT NULL
  `expire_dttm` datetime NOT NULL
  `fee_business_description` text
  `fee_requirements` text
  `the_user` varchar(45) NOT NULL default ''
  `MAS_code_string` varchar(45) default ''
  `fab_code` varchar(8) default NULL,
  PRIMARY KEY  (`fee_id`),
  KEY `FPI_IRD` (`visa_fpi`,`mastercard_ird`,`credit_debit_card_flag`)
) ENGINE=MyISAM AUTO_INCREMENT=1310 DEFAULT CHARSET=latin1
0
 

Author Comment

by:Geoff Millikan
ID: 19992273
Ok, I got it working but I'm not sure if what I did is what caused it to work but if you're reading this maybe it will help you.  On the above query I did a NATURAL JOIN.  I changed this to a INNER JOIN and it seemed to work.  I think really either one of them should have worked but the INNER JOIN really did make it work:

UPDATE
  sharkey s INNER JOIN fee_programs f
  ON s.fee_id=f.fee_id
  set s.fab_code=f.fab_code
0
 
LVL 12

Accepted Solution

by:
AdrianSRU earned 1500 total points
ID: 19992406
There must be some sort of bug in the version that you are using because there shouldn't be any difference at all in the way those two join methods work.


--Adrian
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

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 post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month16 days, 15 hours left to enroll

864 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