Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following 2 tables (places & offer_company) in a MySQL database named COUPON1.81.

CREATE TABLE IF NOT EXISTS `places` (
 `id` int(60) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `Description` text NOT NULL,
  **'Company_id' int(60) NOT NULL,**
 `Active` int(60) NOT NULL, [...]

CREATE TABLE IF NOT EXISTS `offer_company` (
 `id` int(60) NOT NULL AUTO_INCREMENT, 
 `company_name` varchar(150) NOT NULL,
 `Address` text NOT NULL,
 **'places_id' int(60) NOT NULL,** [...]

I need to update the rows from table company_id to table places when insert a new row on table company.

For example, if I insert a new row to offer_company with these values ('1', CSB(pvt)limited, srilanka, 123) then the the trigger should update the rows from table company_id to table places

Places_id select form places table

***places table***

+----------+-------------+------+-----+---------+-------+-------+-----+
| id       | name        | Description                  | Company_id  |
+----------+-------------+------+-----+---------+-------+-------+-----+
| 123      | eiffel tower|  is an iron lattice tower    |  0          | 
| 124      | sigiriya    |  sigiriya Rock               |  0          | 
+----------+-------------+------+-----+---------+-------+-------+-----+


*** Offer_company table ***

+----------+-------------+------+-----+----------+----------+
| id       | company_name       | Address        | places_id|
+----------+-------------+------+-----+----------+----------+
| 1        | CSB(pvt)limited    |  89/A,Srilanka |  123     | 
+----------+-------------+------+-----+----------+----------+

After select places_id in company table, company_id should be updated in places table.

*** places table***


+----------+-------------+------+-----+---------+-------+-------+-------+
| id       | name        | Description                  | Company_id    | 
+----------+-------------+------+-----+---------+-------+-------+-------+
| 123      | eiffel tower|  is an iron lattice tower    |  1            | 
| 124      | sigiriya    | sigiriya Rock                |  0            | 
+----------+-------------+------+-----+---------+-------+-------+-------+

I added this trigger

--
-- Triggers `offer_company`
--
DROP TRIGGER IF EXISTS `add`;
DELIMITER //
CREATE TRIGGER `add` AFTER INSERT ON `offer_company`
 FOR EACH ROW INSERT INTO places (Company_id)
    SELECT new.id 
            FROM offer_company
            WHERE offer_company.places_id = new.places_id
//
DELIMITER ;

How can I solve this problem?

share|improve this question
    
Other than the fact that your question says you want to update but the trigger tries to insert, you haven't actually stated the "problem" you are trying to solve. –  Michael - sqlbot Sep 7 '13 at 18:08
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.