Geoff Millikan
asked on
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
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
ASKER
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'
...
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'
...
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
--Adrian
ASKER
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_identifi er` 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_ir d`,`credit _debit_car d_flag`)
) ENGINE=MyISAM AUTO_INCREMENT=1310 DEFAULT CHARSET=latin1
`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_identifi
`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`
`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_ir
) ENGINE=MyISAM AUTO_INCREMENT=1310 DEFAULT CHARSET=latin1
ASKER
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
UPDATE
sharkey s INNER JOIN fee_programs f
ON s.fee_id=f.fee_id
set s.fab_code=f.fab_code
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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