Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

update with left join in postgresql

Tags:

sql

postgresql

How to write a query for updating a table that has two left joins. Below is a query in MSSQL, I want to convert it into postgresql. Please help.

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
like image 357
Akhilesh Avatar asked Dec 16 '22 00:12

Akhilesh


1 Answers

This is possible in postgres too, 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).

Your query converted:

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"

But why use self-join here? (if "ID" is the primary key) I think you can achive your goal simpler, with:

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

Edit: 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.)

like image 92
pozs Avatar answered Dec 30 '22 11:12

pozs