Re: What is wrong with this SQL

Thanks Chad,
I 'll have a go at it. One thing though if I want to update the price as an
increase in terms of a percent i.e. instead of it being 200 to have it +15%
how would you go about in achieving this.

Is it possible to add a formula after the SET = ((price*0.15)+price)

Cheers

Norman

-----Original Message-----
From: Chad Thompson [mailto:chad(at)weblinkservices(dot)com]
Sent: 14 August 2002 21:29
To: norman(at)khine(dot)net
Subject: Re: [NOVICE] What is wrong with this SQL

could it be that you want this?

update price
set price.price = 200
where(price RIGHT JOIN product ON price.product = product.id) as price RIGHT
JOIN
supplier ON product.supplier = supplier.id
WHERE (((supplier.id)=4) AND ((supplier.status)=1) AND
((product.status)=1) AND ((price.type)=1) AND ((product.id)=100902));

Im not sure if this works, but it should start you down the correct road.

Thanks
Chad
> ----- Original Message -----
> From: "Patrick Hatcher" <PHatcher(at)macys(dot)com>
> To: <norman(at)khine(dot)net>
> Cc: "Pgsql-Novice" <pgsql-novice(at)postgresql(dot)org>;
> <pgsql-novice-owner(at)postgresql(dot)org>
> Sent: Wednesday, August 14, 2002 2:13 PM
> Subject: Re: [NOVICE] What is wrong with this SQL
>
>
> >
> > It appears you aren't updating any fields (or I'm looking at it wrong):
> >
> > UPDATE SOME_TABLE
> > SET FIELD1 = SOME_VALUE
> > From
> > (price RIGHT JOIN product ON price.product = product.id) RIGHT JOIN
> > supplier ON product.supplier = supplier.id SET price.price = 200
> > WHERE (((supplier.id)=4) AND ((supplier.status)=1) AND
> > ((product.status)=1) AND ((price.type)=1) AND ((product.id)=100902));
> >
> >
> > Patrick Hatcher
> >
> >
> >
> >
> > |--------+--------------------------------->
> > | | "Norman Khine" |
> > | | <norman(at)khine(dot)net> |
> > | | Sent by: |
> > | | pgsql-novice-owner(at)post|
> > | | gresql.org |
> > | | |
> > | | |
> > | | 08/14/2002 12:56 PM |
> > |--------+--------------------------------->
> >
>
>---------------------------------------------------------------------------
> -------------------------------|
> > |
> |
> > | To: "Pgsql-Novice" <pgsql-novice(at)postgresql(dot)org>
> |
> > | cc:
> |
> > | Subject: [NOVICE] What is wrong with this SQL
> |
> >
>
>---------------------------------------------------------------------------
> -------------------------------|
> >
> >
> >
> >
> > Hello,
> > I am a bit stuck on this UPDATE query
> >
> > UPDATE (price RIGHT JOIN product ON price.product = product.id) RIGHT
JOIN
> > supplier ON product.supplier = supplier.id SET price.price = 200
> > WHERE (((supplier.id)=4) AND ((supplier.status)=1) AND
> > ((product.status)=1) AND ((price.type)=1) AND ((product.id)=100902));
> >
> > This works.... but the above query gets a exception error "("
> >
> > SELECT supplier.id, price.price, product.id FROM (price RIGHT JOIN
> > product ON price.product = product.id) RIGHT JOIN supplier ON
> > product.supplier = supplier.id WHERE (((su
> > pplier.id)=4) AND ((supplier.status)=1) AND ((product.status)=1) AND
> > ((price.type)=1) AND ((product.id)=100902));
> >
> > Thanks
> >
> > zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
> > zz/********/z/****\zzz|****\*\zz|*******|z
> > z/^^^^^^^^/z/******\zz|*^^^^|*|z|*|^^^^^|z norman khine
> > zzzzzz/**/z|**/^^\**|z|*|zzz|*|z|*|zzzzzzz mailto:norman(at)khine(dot)net
> > zzzzz/**/zz|*|zzzz|*|z|****/*/zz|*****|zzz purley
> > z/******/zz|*|zzzz|*|z|*|^^zzzzz|*|^^^|zzz UK
> > zzZ/**/zzzz|**\^^/**|z|*|zzzzzzz|*|zzzzzzz
> > zz/******/zz\******/zz|*|zzzzzzz|*|*****|z
> > z/^^^^^^/zzzz\^^^^/zzz|^|zzzzzzz|^^^^^^^|z
> > zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> >
>