• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1046
  • Last Modified:

UPDATE one table based on JOIN of two tables. MySQL5

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
Geoff Millikan
Asked:
Geoff Millikan
  • 3
  • 3
1 Solution
 
AdrianSRUCommented:
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
 
Geoff MillikanAuthor Commented:
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
 
AdrianSRUCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Geoff MillikanAuthor Commented:
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
 
Geoff MillikanAuthor Commented:
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
 
AdrianSRUCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now