Join in update - for postgres

[bayswater]

Hi experts,

I have this update, which DOESN'T work when my page hooks up to a postgres database:

pg_query("UPDATE ".$prefix."course c INNER JOIN ".$prefix."course_categories cat ON c.category = cat.id SET c.visible = '1' WHERE c.category = '".$vist."' AND c.visible = '0' AND c.category <> '0'");

Is there a conflict with the JOIN in an update when it comes to postgres, or...?

My identical code for Mysql is working fine:

mysql_query("UPDATE ".$prefix."course c INNER JOIN ".$prefix."course_categories cat ON c.category = cat.id SET c.visible = '1' WHERE c.category = '".$vist."' AND c.visible = '0' AND c.category <> '0'");

[samuelcook]

i don't believe that you have grabbed any information for ".$prefix."course_categories

mysql_query("UPDATE ".$prefix."course c, (SELECT id FROM ".$prefix."course_categories) cat INNER JOIN ".$prefix."course_categories cat ON c.category = cat.id SET c.visible = '1' WHERE c.category = '".$vist."' AND c.visible = '0' AND c.category <> '0'");

This code is wrong, but I believe that you need to select that information first so that you can use it.

[bayswater]

samuelcook;10973310 wrote:

i don't believe that you have grabbed any information for ".$prefix."course_categories

mysql_query("UPDATE ".$prefix."course c, (SELECT id FROM ".$prefix."course_categories) cat INNER JOIN ".$prefix."course_categories cat ON c.category = cat.id SET c.visible = '1' WHERE c.category = '".$vist."' AND c.visible = '0' AND c.category <> '0'");

This code is wrong, but I believe that you need to select that information first so that you can use it.

Hey Samuel, I'm very sorry - I now realize that I pasted the wrong SQL :queasy: I have 2 almost identical and in this second - I NEED a join (I guess?) to the categories table:

mysql_query("UPDATE ".$prefix."course c INNER JOIN ".$prefix."course_categories cat ON c.category = cat.id SET c.visible = '1' WHERE cat.path like '%/".$vist."/%' AND c.visible = '0' AND c.category <> '0'");