Postgres Pro
Downloads
Home   >   mailing lists

Thread: UPDATE with JOIN

UPDATE with JOIN

From
"Keith Worthington"
Date:
Hi All,

I am banging my head againset the wall trying to figure out how to do a JOIN
within an UPDATE.  Can someone please help me out?  Here is what I have.  Of
course it does not work. :-(

UPDATE tbl_line_item
   SET tbl_line_item.reviewed = TRUE
  FROM tbl_item
    ON ( tbl_line_item.item_id = tbl_item.id )
 WHERE item_type = 'DIR';

Kind Regards,
Keith

Re: UPDATE with JOIN

From
Tom Lane
Date:
"Keith Worthington" <keithw@narrowpathinc.com> writes:
> UPDATE tbl_line_item
>    SET tbl_line_item.reviewed = TRUE
>   FROM tbl_item
>     ON ( tbl_line_item.item_id = tbl_item.id )
>  WHERE item_type = 'DIR';

Of course that's not valid JOIN syntax (no JOIN keyword, and no place to
put it either).  You have to use the WHERE clause:

UPDATE tbl_line_item
   SET tbl_line_item.reviewed = TRUE
  FROM tbl_item
 WHERE tbl_line_item.item_id = tbl_item.id
       AND item_type = 'DIR';

            regards, tom lane

Re: UPDATE with JOIN

From
"Keith Worthington"
Date:
On Tue, 24 May 2005 14:26:21 -0400, Tom Lane wrote
> "Keith Worthington" <keithw@narrowpathinc.com> writes:
> > UPDATE tbl_line_item
> >    SET tbl_line_item.reviewed = TRUE
> >   FROM tbl_item
> >     ON ( tbl_line_item.item_id = tbl_item.id )
> >  WHERE item_type = 'DIR';
>
> Of course that's not valid JOIN syntax (no JOIN keyword, and no
> place to put it either).  You have to use the WHERE clause:
>
> UPDATE tbl_line_item
>    SET tbl_line_item.reviewed = TRUE
>   FROM tbl_item
>  WHERE tbl_line_item.item_id = tbl_item.id
>        AND item_type = 'DIR';
>
>             regards, tom lane
>

Thanks Tom

I couldn't figure out how to do the JOIN part of that statement.  Using your
instruction all I had left to do was drop the qualification off the SET column
and it worked.  :-)  Thanks again.

UPDATE tbl_line_item
   SET reviewed = TRUE
  FROM tbl_item
 WHERE tbl_line_item.item_id = tbl_item.id
   AND item_type = 'DIR';

Kind Regards,
Keith

Re: UPDATE with JOIN

From
"Keith Worthington"
Date:
On Tue, 24 May 2005 16:31:15 -0400, Keith Worthington wrote
> On Tue, 24 May 2005 14:26:21 -0400, Tom Lane wrote
> > "Keith Worthington" <keithw@narrowpathinc.com> writes:
> > > UPDATE tbl_line_item
> > >    SET tbl_line_item.reviewed = TRUE
> > >   FROM tbl_item
> > >     ON ( tbl_line_item.item_id = tbl_item.id )
> > >  WHERE item_type = 'DIR';
> >
> > Of course that's not valid JOIN syntax (no JOIN keyword, and no
> > place to put it either).  You have to use the WHERE clause:
> >
> > UPDATE tbl_line_item
> >    SET tbl_line_item.reviewed = TRUE
> >   FROM tbl_item
> >  WHERE tbl_line_item.item_id = tbl_item.id
> >        AND item_type = 'DIR';
> >
> >             regards, tom lane
> >
>
> Thanks Tom
>
> I couldn't figure out how to do the JOIN part of that statement.
>  Using your instruction all I had left to do was drop the
> qualification off the SET column and it worked.  :-)  Thanks again.
>
> UPDATE tbl_line_item
>    SET reviewed = TRUE
>   FROM tbl_item
>  WHERE tbl_line_item.item_id = tbl_item.id
>    AND item_type = 'DIR';
>
> Kind Regards,
> Keith
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

Replying to myself in the hopes of helping someone else.

I wondered (and needed to know) if this concept could be extended to use
multiple tables?  The answer is yes.  The syntax of the query seemed different
to me so I repeat it here.

 UPDATE tbl_line_item
    SET reviewed = TRUE
   FROM tbl_item_bom,
        tbl_item
  WHERE tbl_line_item.so_number = tbl_item_bom.so_number
    AND tbl_line_item.so_line = tbl_item_bom.so_line
    AND tbl_line_item.item_id::text = tbl_item.id::text
    AND tbl_item_bom.so_subline IS NOT NULL
    AND (    tbl_item.item_type::text = 'THIS'::text
          OR tbl_item.item_type::text = 'THAT'::text
          OR tbl_item.item_type::text = 'OTHR'::text
        );

Kind Regards,
Keith