mysql更新查询与内部联接 [英] mysql update query with inner join

问题描述

我有两个表customer和order.我想用一个查询更新两个表中的不同值.例如，客户表有一个城市列，值是德国，而订单表有状态列，值是hold，我想将德国更改为伦敦，并用一个查询将其保持为已解决.这是下面的查询

I have two tables customer and order. I want to update different values in both tables with one query. For example customer table has a city column and value is germany and order table has status column and value is hold, I want to change germany to london and hold to resolved with one query. Here is the query below

UPDATE customer,order INNER JOIN order ON customer.cust_id = order.cust_id SET cust_city = 'Lahore' AND order_status= 'Resolved' WHERE cust_id = 2 

mysql显示此查询错误

mysql is showing error for this query

推荐答案

MySQL支持此操作:

MySQL supports this operation:

UPDATE customer c INNER JOIN
       order o
       ON c.cust_id = o.cust_id
    SET c.cust_city = 'Lahore',
        o.order_status = 'Resolved'
    WHERE c.cust_id = 2 ;

注意:order对于表来说确实是一个坏名字，因为它是一个SQL关键字.为不需要转义的内容选择名称.

Note: order is a really bad name for a table, because it is a SQL keyword. Choose names for things that do not need to be escaped.

这篇关于mysql更新查询与内部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！