Updating another table with data from main table |
It is often required to use one table and update data in another table. The second table columns gets updated by taking data from first table. Let us say we have one table where students test marks are stored along with other details in other columns. We can collect only the test data and keep them in a separate table. Let us call this second table as student mark table. Both the tables will have student id field which we will be using to link both tables
update s_mark,s_test set mark= test1 where s_mark.s_id=s_test.s_id
Here are both tables for you.
CREATE TABLE `s_mark` (
`s_id` int(3) NOT NULL,
`mark` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `s_mark`
--
INSERT INTO `s_mark` VALUES (1, 7);
INSERT INTO `s_mark` VALUES (2, 6);
-- --------------------------------------------------------
--
-- Table structure for table `s_test`
--
CREATE TABLE `s_test` (
`s_id` int(3) NOT NULL,
`test1` int(3) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
If you are facing any problem in viewing this page, please tell us
