sql - 在postgresql中用左连接更新

Question

如何编写更新具有两个左连接的表的查询。下面是 MSSQL 中的查询，我想将其转换为 postgresql。请帮忙。

Update T1 Set  
            T1.Amount = (T1.Amount - T2.Disc) + ((DT1.N_Amount)/2)

    From @Before T1
    Left Join @Before T2 On T1.ID = T2.ID
    Left Join @DiscTable DT1 On T1.PurchID = DT1.Purch_ID

Answer 1

这在 postgres 中也是可能的，the main difference is

Note that the target table must not appear in the from_list, unless you intend a self-join (in which case it must appear with an alias in the from_list).

您的查询已转换:

UPDATE "Before" "T1"
SET "T1"."Amount" = ("T1"."Amount" - "T2"."Disc") + (("DT1"."N_Amount")/2)
FROM "Before" "T2"
LEFT JOIN "DiscTable" "DT1" ON "T1"."PurchID" = "DT1"."Purch_ID"
WHERE "T1"."ID" = "T2"."ID"

但是这里为什么要用self-join呢？ (如果 "ID" 是主键)我认为您可以更简单地实现您的目标，方法是:

UPDATE "Before" "T1"
SET "T1"."Amount" = ("T1"."Amount" - "T1"."Disc") + (("DT1"."N_Amount")/2)
FROM "DiscTable" "DT1"
WHERE "T1"."PurchID" = "DT1"."Purch_ID"

编辑:about quoting :

Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)