时间戳

首页 > 解决方案 > 从不同的表数据更新表行不起作用

php - 从不同的表数据更新表行不起作用

问题描述

我的数据库中有两个表。一个叫wp_tickets，另一个叫wp_tickets_regenerated。

如果匹配，我正在尝试wp_tickets_regenerated从表中更新表中的一些数据，因此我最终得到一个新表，其中为该产品下的每个订单生成了不同的票号。wp_ticketslottery_id

到目前为止，我的代码抛出了一个错误：

 WordPress database error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM wp_tickets
        WHERE lottery_id = wp_tickets.lottery_id' at line 6 for query UPDATE wp_tickets_regenerated

有人可以帮忙吗？

wp_tickets 表

+---------+-----------+-----------+---------------+----------+------------+
| user_id | full_name | answer_id | ticket_number | order_id | lottery_id |
+---------+-----------+-----------+---------------+----------+------------+
| 0       | test      | 1         | 3             | 791      | 790        |
+---------+-----------+-----------+---------------+----------+------------+
| 0       | test      | 1         | 5             | 791      | 790        |
+---------+-----------+-----------+---------------+----------+------------+
| 0       | test      | 1         | 10            | 791      | 790        |
+---------+-----------+-----------+---------------+----------+------------+

wp_tickets_regenerated

   +---------+-----------+-----------+---------------+----------+------------+
    | user_id | full_name | answer_id | ticket_number | order_id | lottery_id |
    +---------+-----------+-----------+---------------+----------+------------+
    |         |           |           | 1             |          | 790        |
    +---------+-----------+-----------+---------------+----------+------------+
    |         |           |           | 2             |          | 790        |
    +---------+-----------+-----------+---------------+----------+------------+
    |         |           |           | 3             |          | 790        |
    +---------+-----------+-----------+---------------+----------+------------+

到目前为止我所拥有的是：

 $wpdb->query(
       'UPDATE wp_tickets_regenerated
        SET user_id = wp_tickets.user_id,
            full_name = wp_tickets.full_name,
            answer_id = wp_tickets.answer_id,
            order_id = wp_tickets.order_id
        FROM wp_tickets
        WHERE lottery_id = wp_tickets.lottery_id'
  );

标签： phpmysqldatabasewordpress

解决方案

您需要一个内部连接表wp_tickets进行更新

尝试这个

UPDATE wp_tickets_regenerated
INNER JOIN wp_tickets ON wp_tickets_regenerated.lottery_id = wp_tickets.lottery_id
SET wp_tickets_regenerated.user_id = wp_tickets .user_id,
    wp_tickets_regenerated.full_name = wp_tickets .full_name,
    wp_tickets_regenerated.answer_id = wp_tickets .answer_id,
    wp_tickets_regenerated.order_id = wp_tickets .order_id
WHERE wp_tickets_regenerated.lottery_id = wp_tickets.lottery_id'

希望对你有帮助

推荐阅读