CODEWITHSUNDEEP
phpmysql
Renee Cribe
Renee Cribe

Reputation: 325

From a MySQL result update a column automatically

I have a sql query that I use to create a view. Would it be possible that while that query runs, it will update a corresponding field from a table and assign a user?

For example: My query, will give me this view:

   dealID |  removalnotes  | rolloverenabled   | rateplanchanges
   1      |  Yes           | Yes               | NULL
   2      | Null           | Null              | NULL
   3      | Null           | Yes               | NULL
   4      | Null           | Yes               | Yes

Here is my query:

SELECT dealID, removalnotes, rolloverenabled , rateplanchanged
FROM invoice_payment
WHERE removalnotes IS NULL OR removalnotes <> 'Yes' 
OR rolloverenabled IS NULL OR rolloverenabled <> 'Yes' 
OR rateplanchanged IS NULL OR rateplanchanged <> 'Yes'
GROUP BY dealID

Can I add a subquery there that will do this automatically? If I can, where exactly do I place or how do I place the subquery?

UPDATE invoice_payment SET
user = 'User1'
WHERE dealID = dealID

Upvotes: 0

Views: 61

Answers (2)

Afsar
Afsar

Reputation: 3124

Try this,

           UPDATE 
              invoice_payment AS ip
            CROSS JOIN (
              SELECT dealID, removalnotes, rolloverenabled , rateplanchanged
            FROM invoice_payment
            WHERE removalnotes IS NULL OR removalnotes <> 'Yes' 
            OR rolloverenabled IS NULL OR rolloverenabled <> 'Yes' 
            OR rateplanchanged IS NULL OR rateplanchanged <> 'Yes'
            GROUP BY dealID

            ) AS sq
            SET 
              ip.user = 'User1' 
            WHERE ip.dealID = dealID

Upvotes: 1

Zuko
Zuko

Reputation: 13

You can add a trigger, perhaps it can be solved using it. http://dev.mysql.com/doc/refman/5.0/es/triggers.html

Upvotes: 0

Related Questions