使用order by和limit-mysql在单个查询中更新两个不同表的两列

问题描述

查询

 UPDATE vcd_resorts AS resorts,
           vcd_deals AS deals
    SET    resorts.rst_live_date = Date_add(Curdate(), INTERVAL 4 day),
           deals.del_date = Date_add(Curdate(), INTERVAL 4 day)
    WHERE  0 = (SELECT resort_id_count
                FROM   (SELECT Count(rst_id) AS resort_id_count
                        FROM   vcd_resorts
                        WHERE  rst_supersaver_resort = 1
                               AND rst_live_date BETWEEN Curdate() + 1 AND
                                                         Curdate() + 4)
                       temp)
           AND resorts.rst_supersaver_resort = 1
           AND resorts.rst_id = deals.del_resort_id
           AND deals.del_supersaver_deal = 1
    ORDER BY resorts.rst_live_date ASC
    LIMIT 1

错误

此方法以及执行此操作的任何其他方法有什么问题

what is wrong in this and any other way to do this

推荐答案

我已经解决了这个问题

UPDATE vcd_resorts AS resorts,
                vcd_deals AS deals
                SET    resorts.rst_live_date = Date_add(Curdate(), INTERVAL 4 day),
                       deals.del_date = Date_add(Curdate(), INTERVAL 4 day)
                WHERE  0 = (SELECT resort_id_count
                            FROM   (SELECT Count(rst_id) AS resort_id_count
                                    FROM   vcd_resorts
                                    WHERE  rst_supersaver_resort = 1
                                           AND rst_live_date BETWEEN Curdate() + 1 AND
                                                                     Curdate() + 4)
                                   temp)
                AND resorts.rst_supersaver_resort = 1
                AND resorts.rst_id = deals.del_resort_id
                AND deals.del_supersaver_deal = 1
                AND resorts.rst_id = (SELECT resort_id
                                    FROM   (SELECT rst_id AS resort_id
                                             FROM   vcd_resorts
                                             WHERE  rst_supersaver_resort = 1
                                             ORDER  BY rst_live_date ASC
                                             LIMIT  1) temp1)

我又提出了一个条件，以条件"和限价"代替了订单

i have placed one more condition which replaced order by and limit

这篇关于使用order by和limit-mysql在单个查询中更新两个不同表的两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！