Обсуждение: Converting SQL to pg

I must be tired. Any suggestions on converting the following to postgresql?

UPDATE IGNORE yose5_user_newflags AS flags, yose5_messages AS
msg SET flags.forum_id = msg.forum_id WHERE flags.message_id =
msg.message_id AND flags.message_id IN (15580, 15581, 15706,
15712, 15713, 15719, 15888)

martin wrote:
> I must be tired. Any suggestions on converting the following
> to postgresql?
>
> UPDATE IGNORE yose5_user_newflags AS flags, yose5_messages AS
> msg SET flags.forum_id = msg.forum_id WHERE flags.message_id =
> msg.message_id AND flags.message_id IN (15580, 15581, 15706,
> 15712, 15713, 15719, 15888)

To PostgreSQL from what?
Can you explain what the statement is supposed to do?

Yours,
Laurenz Albe

In article <D960CB61B694CF459DCFB4B0128514C203937E64@exadv11.host.magwien.gv.at>,
Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
>martin wrote:

>To PostgreSQL from what?

Mysql unless the person try to port the code made some changes to it.

>Can you explain what the statement is supposed to do?

It's updating a table of what messages have been read during a thread
move. It's part of phorum (http://www.phorum.org) which only has mysql
officially supported.

martin@cornhobble.com wrote:
> I must be tired. Any suggestions on converting the following to postgresql?
>
> UPDATE IGNORE yose5_user_newflags AS flags, yose5_messages AS
> msg SET flags.forum_id = msg.forum_id WHERE flags.message_id =
> msg.message_id AND flags.message_id IN (15580, 15581, 15706,
> 15712, 15713, 15719, 15888)
>
>

Untested, but I imagine it would be something like this.

UPDATE yose5_user_newflags AS flags
SET    forum_id = msg.forum_id
FROM   yose5_messages AS msg
WHERE  flags.message_id = msg.message_id AND flags.message_id IN (15580,
15581, 15706, 15712, 15713, 15719, 15888)

PostgreSQL doesn't have anything similar to IGNORE (afaik), but
depending on what you want to do, you may be able to work around that
with deferred constraints etc.

--
Tommy Gildseth
DBA, Gruppe for databasedrift
Universitetet i Oslo, USIT
m: +47 45 86 38 50
t: +47 22 85 29 39