How to go through each row of the database compare and update a specific column?

Question

I have a table called PRODUCT and I need to make a comparison between two columns, the column DATA_INICIAL AND DATA_FINAL, for each row travelled, if the product is not in agreement should enter the value 0 in the column PROD_ATIVO.

Rule: If the initial date is greater than the current date or the final date is less than the present date the product should not be displayed.

Product(Exemplification):

Product name: Danoninho
Product display date: 27/03/2017 to 30/03/2017.

Answer 1

If DATA_FINAL is already smaller than the current date, DATA_INICIAL will never be larger than the current date. So just update the PROD_ATIVO column when DATA_FINAL is less than the current date.

 Update PRODUTO SET PROD_ATIVO='0' where DATA_FINAL < now()

But if there is no certainty that the dates in the bank have been entered correctly you can do so

 Update PRODUTO set PROD_ATIVO='0' where DATA_FINAL < now() or Data_INICIAL > now()

Answer 2

In the tags says it is mysql so you need to perform an update in the product table, it would be more or less like this:

UPDATE produto
SET PROD_ATIVO = 0
WHERE 
DATA_INCIAL > DATE("now")
OR DATA_FINAL < DATE("now");

Here we say: Update all records and change PROD_ATIVO to 0 where the initial date is greater than the current date (mysql date function) or the final data_is less than the current date;

mysql date functions: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date

EDIT: Assuming that the date fields are like timestamp or date, if they are like string there only converting to dates, the date function does this too...

Answer 3

You need to give a UPDATE. In the Tutorial Points has a page on the subject:

Using Mysql:

UPDATE produto
SET PROD_ATIVO =
    CASE
        WHEN DATA_INICIAL <= now() AND DATA_FINAL > now() THEN 1
        ELSE 0
    END

EDIT

As mentioned by @rock.ownar, the problem is update using multiple tables.

You can check the mysql syntax for this; also has the alternative of using the second table only in the clause where, as a subquery.

UPDATE produto, tb_cupons
SET produto.prod_ativo = 0
WHERE
   produto.produto_id = tb_cupons.produto_id AND
   (
       tb_cupons.data_inicial < now() OR
       tb_cupons.data_final > now()
   )