[contractdashboard.git] / admin / fixMissingABN.sql
--- a/admin/fixMissingABN.sql
+++ b/admin/fixMissingABN.sql
@@ -1,1 +1,19 @@
+UPDATE contractnotice
+SET "supplierABN" = subquery."supplierABN"
+FROM (SELECT
+        "CNID",
+        a."supplierABN"
+      FROM contractnotice
+        INNER JOIN
+        (SELECT
+                      text_mode("supplierABN") AS "supplierABN",
+                      lower(regexp_replace("supplierName",'[,\.]','', 'g'))    AS name
+                    FROM contractnotice
+                    WHERE "supplierABN" IS NOT NULL
+                    GROUP BY name) a ON
+                                      lower(regexp_replace("supplierName",'[,\.]','', 'g')) = name
+      WHERE contractnotice."supplierABN" IS NULL)
+  AS subquery
+WHERE contractnotice."CNID" = subquery."CNID";
 
+