用SQL从一个表更新另一个表的最佳方式是什么?

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

如何更新product_visited中所包含的列，如果是product_visited。idproduct = product_commanded。idproduct 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

当logdate和idproduct的值在product_command中存在时，命令= 1，这意味着访问的产品也会被调用。

3 个解决方案

#1

I believe this is what you are looking for:

我相信这就是你想要的:

Update product_visited pv
    set commanded = 1
    Where exists (Select 1
                  from product_commanded pc
                  where pv.idproduct = pc.idproduct and pv.logdate = pc.date
                 );

#2

Ok, I've made guesses with the join fields but you're after something like this;

好的，我已经用join字段进行了猜测，但是你要做的是这样的事情;

UPDATE pv
SET pv.Commanded = 1
FROM Product_Visited pv
JOIN Product_Commanded pc
    ON pv.logdate = pc.date
    AND pv.idproduct = pc.id

The inner join means that you're only going to update records in Product_Visited where there are matching rows in Product_Commanded based on the join predicates you give it.

内部连接意味着您将只更新Product_Visited中的记录，其中根据您提供的连接谓词在product_command中有匹配的行。

Note: this is a SQL Server answer. May or may not work in MySQL

注意:这是SQL服务器的答案。是否可以在MySQL中工作

#3

Sounds like you want to update commanded whenever a record exists for same product in commanded table?

听起来好像你想要更新命令，当一个记录在命令表中存在时?

in any database:

在任何数据库:

Update product_visited set commanded = 1
Where exists(Select * from product_commanded
             where product_id = product_visited.Product_id)

#1