Update One Table from Data in Another Table

[transfield]

Hello,

 

I have 2 tables. The structures are below:-

 

CREATE TABLE IF NOT EXISTS `dcp_reports_raw` (
`id` int(6) NOT NULL auto_increment,
`club_num` int(10) NOT NULL,
`club_name` varchar(60) NOT NULL,
`mship_base` int(3) NOT NULL,
`mship_todate` int(3) NOT NULL,
`dcp_todate` int(2) NOT NULL,
KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0;

CREATE TABLE IF NOT EXISTS `dcp_reports` (
`id` int(6) NOT NULL auto_increment,
`club_num` int(10) NOT NULL,
`club_name` varchar(60) NOT NULL,
`mship_base` int(3) NOT NULL,
`mship_todate` int(3) NOT NULL,
`dcp_todate` int(2) NOT NULL,
`mship_lastweek` int(3) NOT NULL,
`dcp_lastweek` int(2) NOT NULL,
`to_email` varchar(100) NOT NULL,
`from_email` varchar(50) NOT NULL,
`message` varchar(1000) NOT NULL,
`database_lastupdated` date NOT NULL,
`email_lastsent` date NOT NULL,
`expiry` date NOT NULL,
KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0;

I want to :-

 

1. update dcp_reports.mship_todate with the data from dcp_reports_raw.mship_todate provided that the values of dcp_reports.club_num and dcp_reports_raw.club_num are the same.

 

2. update dcp_reports.dcp_todate with the data from dcp_reports_raw.dcp_todate provided that the values of dcp_reports.club_num and dcp_reports_raw.club_num are the same.

 

If both item 1 and 2 can be combined in 1 query, so much the better. Otherwise, I'm okay with 2 separate queries.

 

Could you show me how to write the code in PHP or MySql please?

 

Thanks.

[trq]

Why do you want to duplicate data in your database? This generally flies in the face of good design.