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
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 |