Lists: | pgsql-novice |
---|
From: | "Keith Worthington" <keithw(at)narrowpathinc(dot)com> |
---|---|
To: | "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org> |
Subject: | UPDATE with JOIN |
Date: | 2005-05-24 18:02:40 |
Message-ID: | 20050524175811.M92003@narrowpathinc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | KeithW(at)narrowpathinc(dot)com |
Cc: | "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: UPDATE with JOIN |
Date: | 2005-05-24 18:26:21 |
Message-ID: | 4584.1116959181@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
"Keith Worthington" <keithw(at)narrowpathinc(dot)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
From: | "Keith Worthington" <keithw(at)narrowpathinc(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: UPDATE with JOIN |
Date: | 2005-05-24 20:31:15 |
Message-ID: | 20050524202906.M48823@narrowpathinc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
On Tue, 24 May 2005 14:26:21 -0400, Tom Lane wrote
> "Keith Worthington" <keithw(at)narrowpathinc(dot)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
From: | "Keith Worthington" <keithw(at)narrowpathinc(dot)com> |
---|---|
To: | "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: UPDATE with JOIN |
Date: | 2005-05-31 13:01:02 |
Message-ID: | 20050531124816.M57906@narrowpathinc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
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(at)narrowpathinc(dot)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