I have 2 tables the first one is the product-page visited
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| idproduct | varchar(128) | YES | | NULL | |
| logdate | date | YES | | NULL | |
| idmagasin | int(20) | YES | | NULL | |
| idenseigne | int(20) | YES | | NULL | |
| commanded | int(2) | YES | | 0 | |
+------------+--------------+------+-----+---------+----------------+
And the second one is the product commanded
+-------------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| idproduct | varchar(255) | NO | | NULL | |
| idenseigne | int(11) | NO | | NULL | |
| idmagasin | int(11) | NO | | NULL | |
| ingredients | tinytext | YES | | NULL | |
| date | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------------+--------------+------+-----+-------------------+----------------+
How can i update the column commanded in product_visited , if product_visited.idproduct = product_commanded.idproduct and product_visited.logdate = product_commanded.date
i'm confused to use inner join or exists
I want to update product_visited.commanded = 1
when the value of logdate
and idproduct
exists in product_commanded, it will mean the product visited is commanded too
I believe this is what you are looking for: