使用左连接在单个更新语句中更新多个表 [英] Update multiple tables in a single update statement with left join

问题描述

我意识到我的要求可能是不可能的.我想基于 LEFT JOIN 和 WHERE 子句对两个单独的表执行更新.这是我的尝试:

I realise what I'm asking for may be impossible. I want to perform an UPDATE on two separate tables based on a LEFT JOIN and a WHERE clause. This is my attempt:

UPDATE  PERIODDATES as pd, periods2 as p2
SET     pd.[PERIODCODE] = @PERIODCODE,
     p2.[USERCODE] = @USERCODE
             left join periods2 AS p2
    ON  pdates.schemeid = p2.schemeid AND

WHERE [SCHEMEID] = @SCHEMEID

这可能吗?

推荐答案

这是不可能的.一个语句只能更新一张表.您必须将其拆分为两个语句.

This is not possible. You can only update one table in one statement. You will have to split this out in to two statements.

在一个语句中更新两个表实际上是可能的，但需要使用包含要更新的两个表的 UNION 创建一个视图.然后您可以更新视图，然后更新基础表.

Updating two tables in one statement is actually possible but will need to create a View using a UNION that contains both the tables you want to update. You can then update the View which will then update the underlying tables.

这对我来说似乎有点黑客，但它会起作用.

It seems like a bit of hack to me but it will work.

这篇关于使用左连接在单个更新语句中更新多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！