Fixed network deletion error due to incorrect syntax in SQL trigger,

also this is bad practice to modify a migration (they should be
immutable) but didn't want to have one with syntax errors and I will be
refactoring and splitting this one huge migration into multiple separate
migrations later on

1 files changed, 6 insertions, 4 deletions

diff --git a/migrations/20241026174345_init.sql b/migrations/20241026174345_init.sql
index e4f0f78..e22ecf0 100644
--- a/migrations/20241026174345_init.sql
+++ b/migrations/20241026174345_init.sql
@@ -106,10 +106,12 @@ BEGIN
   UPDATE users_networks SET
     position = position - 1
   WHERE user_id IN (
-    SELECT user_id FROM users_networks WHERE network_id = OLD.network_id
-  ) AND position > OLD.position;
-  
-  DELETE FROM users_networks WHERE network_id = OLD.network_id;
+    SELECT user_id FROM users_networks WHERE network_id = OLD.id
+  ) AND position > (
+    SELECT position FROM users_networks WHERE user_id = users_networks.user_id AND network_id = OLD.id
+  );
+
+  DELETE FROM users_networks WHERE network_id = OLD.id;
 END
 -- +goose StatementEnd