postgresql

postgres update columns of same table with specific condition


I work with postgresql 11.

I have a table "portal_courses" with this columns: department_id,course_no,department_mother,status,desc_ar,desc_en this is an example of data :

department_id,    course_no,department_mother,status         desc_ar      desc_en

51                4516      51                1              testAR4516   testEn4516
63                8542      51                1              null         null
28                8886      51                1              null         null
22                8552      51                1              testAR8552   testEn8552
60                1002      39                1              testAR1002    testEn1002
70                9856      70                1              null          null
71                8523      70                1              testAR8523    testEn8523

I want to update desc_ar and desc_en with specific condition. first check if department_id=department_mother and check if desc_ar ,desc_en is not null then update desc_ar,desc_en of all others course_no which have the same department_mother and only update the desc_ar,desc_en which are null.

this is the correct result :

department_id,    course_no,department_mother,status         desc_ar      desc_en

51                4516      51                1              testAR4516   testEn4516
63                8542      51                1              testAR4516   testAR4516
28                8886      51                1              testAR4516   testAR4516
22                8552      51                1              testAR8552   testEn8552
60                1002      39                1              testAR1002    testEn1002
70                9856      70                1              null          null
71                8523      70                1              testAR8523    testEn8523

so only update this lines :

63                8542      51                1              testAR4516   testAR4516
28                8886      51                1              testAR4516   testAR4516

I try without success with this code :

UPDATE 
   portal_courses 
SET 
 desc_ar = p1.desc_ar ,desc_en=p1.desc_en
FROM 
   portal_courses  p1
    
WHERE 
  portal_courses.department_id = p1.department_mother

   and portal_courses.status='1' and portal_courses.desc_ar is  null and portal_courses.desc_en is  null
   
   
  and p1.desc_ar is not null and p1.desc_en is not null

Solution

  • There are two issues with the attempted query which are identified with comments in the following query:

    UPDATE portal_courses
    SET
      desc_ar = p1.desc_ar,
      desc_en = p1.desc_en
    FROM
      portal_courses p1
    WHERE
      -- change portal_courses.department_id to portal_courses.department_mother
      portal_courses.department_mother = p1.department_mother
      AND portal_courses.status = '1'
      AND portal_courses.desc_ar IS NULL
      AND portal_courses.desc_en IS NULL
      -- add condition to identify mother rows
      AND p1.department_id = p1.department_mother
      AND p1.desc_ar IS NOT NULL
      AND p1.desc_en IS NOT NULL
    -- return changed rows to check results
    RETURNING
      portal_courses.*;
    

    Running the query results in the following output which demonstrates that the appropriate rows were updated:

    department_id course_number department_mother status desc_ar desc_en
    63 8542 51 1 testAR4516 testEn4516
    28 8886 51 1 testAR4516 testEn4516