Hi guys:
I work with some grassroot communities, which we have to keep following up. And
we need to know how the population changes in the different years.
What I figured out is to have two tables: one to have the name of the
organization, and the second which contains the changes in the time.
To avoid people having to input the stored data each time (and just update from
the stored data), I am trying to create a query which inserts (re-inserts) the
primary key of the primary table into the Foreign Key field of the secondary
table, adding the current year, and then the user just have to update the
information and it has the year collected from a form variable.
The query is as follows:
INSERT INTO tbl_secondary( FK_ORG, year )
VALUES (
(SELECT PK_ORG
FROM tbl_primary
WHERE Province = 'Province1'), 2006
)
It gives me the following error message: "1242. Subquery returns more than 1
row"
If I try to do the following query it works, but it is not useful for me
because we need to insert the data with its especific year (timestamp is not
useful as well):
INSERT INTO tbl_secondary( FK_ORG )
(
SELECT PK_ORG
FROM tbl_primary
WHERE Province = 'Province1'
)
How could I override this problem?.
Does anybody has a better idea in how to manage this information storage?.
Thanks a lot, and very best regards for all of you.
Alvaro Cobo.
System Characteristics: Mysql 4.1.11 on a Debian Sarge Stable
Next: Example tables with data.
CREATE TABLE `tbl_primary` (
`NameOrg` varchar(255) collate latin1_spanish_ci default NULL,
`Province` varchar(255) collate latin1_spanish_ci default NULL,
`PK_ORG` int(11) NOT NULL auto_increment,
`last_change` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`PK_ORG`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci
COMMENT='Grassroot organizations' AUTO_INCREMENT=7 ;
--
-- Dumping data for table `tbl_primary`
--
INSERT INTO `tbl_primary` VALUES ('Org1', 'Province1', 1, '2005-08-17
23:33:02');
INSERT INTO `tbl_primary` VALUES ('Org2', 'Province1', 2, '2005-08-17
23:33:02');
INSERT INTO `tbl_primary` VALUES ('Org3', 'Province3', 3, '2005-08-17
23:33:32');
INSERT INTO `tbl_primary` VALUES ('Org4', 'Province3', 4, '2005-08-17
23:33:32');
INSERT INTO `tbl_primary` VALUES ('Org5', 'Province4', 5, '2005-08-17
23:33:57');
INSERT INTO `tbl_primary` VALUES ('Org6', 'Province6', 6, '2005-08-17
23:33:57');
--
-- Table structure for table `tbl_secondary`
--
CREATE TABLE `tbl_secondary` (
`FK_OB` int(11) NOT NULL default '0',
`year` year(4) default NULL,
`Number_Family` int(11) default NULL,
`last_change` timestamp NOT NULL default '0000-00-00 00:00:00',
KEY `IdOB` (`FK_OB`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_spanish_ci
COMMENT='Annual meassurement';
