Populate.php with pg

[sbulen]

The UPDATE statement in Populate.php that tidies up board IDs at the end is not pg compliant.

https://github.com/SimpleMachines/tools/blob/368f1c659b365d9893694dea107e32d3d1c2bcf6/Populate.php#L254

[jdarwood007]

Does PG suport CTE with updates?

Something like this maybe then?

;WITH s AS (
    SELECT t.id_topic, t.id_board
    FROM {db_prefix}topics AS t
    INNER JOIN {db_prefix}messages AS m ON (m.id_topic = t.id_topic)
    WHERE t.id_board != m.id_board
)
UPDATE {db_prefix}messages
SET id_board = s.id_board
WHERE id_topic = s.id_topic

[sbulen]

Not sure. I thought we had a specific $smcFunc that abstracted UPDATE... SET..., but I guess not...

[albertlast]

Postgresql cte query:

WITH s AS (
    SELECT t.id_topic, t.id_board
    FROM smf_topics AS t
    INNER JOIN smf_messages AS m ON (m.id_topic = t.id_topic)
    WHERE t.id_board != m.id_board
)
UPDATE smf_messages as m
SET id_board = s.id_board
from s
WHERE m.id_topic = s.id_topic

[jdarwood007]

That should work as well for mysql. I should note when I write CTEs in update, the WITH s I use to mean "source" as in source data. Most of my CTEs use WITH s and then a FROM table AS t (so "target"). We don't have any such syntax defined for SMF code yet. But it makes it clear which is the intended source and target of updates.

[albertlast]

i'm very sure that my sql doesn't work on mysql. mysql below 8.0 is not able to run cte and the update sytax from table should be different.