Обсуждение: 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@khine.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
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@khine.net> |
| | Sent by: |
| | pgsql-novice-owner@post|
| | gresql.org |
| | |
| | |
| | 08/14/2002 12:56 PM |
|--------+--------------------------------->
>----------------------------------------------------------------------------------------------------------|
| |
| To: "Pgsql-Novice" <pgsql-novice@postgresql.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@khine.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@postgresql.org
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@macys.com>
To: <norman@khine.net>
Cc: "Pgsql-Novice" <pgsql-novice@postgresql.org>;
<pgsql-novice-owner@postgresql.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@khine.net> |
> | | Sent by: |
> | | pgsql-novice-owner@post|
> | | gresql.org |
> | | |
> | | |
> | | 08/14/2002 12:56 PM |
> |--------+--------------------------------->
>
>---------------------------------------------------------------------------
-------------------------------|
> |
|
> | To: "Pgsql-Novice" <pgsql-novice@postgresql.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@khine.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@postgresql.org
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
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@weblinkservices.com]
Sent: 14 August 2002 21:29
To: norman@khine.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@macys.com>
> To: <norman@khine.net>
> Cc: "Pgsql-Novice" <pgsql-novice@postgresql.org>;
> <pgsql-novice-owner@postgresql.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@khine.net> |
> > | | Sent by: |
> > | | pgsql-novice-owner@post|
> > | | gresql.org |
> > | | |
> > | | |
> > | | 08/14/2002 12:56 PM |
> > |--------+--------------------------------->
> >
>
>---------------------------------------------------------------------------
> -------------------------------|
> > |
> |
> > | To: "Pgsql-Novice" <pgsql-novice@postgresql.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@khine.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@postgresql.org
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
>
set price = (price * 1.15)
should work just fine
Thanks
Chad
----- Original Message -----
From: "Norman Khine" <norman@khine.net>
To: "Chad Thompson" <chad@weblinkservices.com>
Cc: "Pgsql-Novice" <pgsql-novice@postgresql.org>
Sent: Wednesday, August 14, 2002 5:14 PM
Subject: Re: [NOVICE] 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@weblinkservices.com]
> Sent: 14 August 2002 21:29
> To: norman@khine.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@macys.com>
> > To: <norman@khine.net>
> > Cc: "Pgsql-Novice" <pgsql-novice@postgresql.org>;
> > <pgsql-novice-owner@postgresql.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@khine.net> |
> > > | | Sent by: |
> > > | | pgsql-novice-owner@post|
> > > | | gresql.org |
> > > | | |
> > > | | |
> > > | | 08/14/2002 12:56 PM |
> > > |--------+--------------------------------->
> > >
> >
>
>---------------------------------------------------------------------------
> > -------------------------------|
> > > |
> > |
> > > | To: "Pgsql-Novice" <pgsql-novice@postgresql.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@khine.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@postgresql.org
> > >
> > >
> > >
> > >
> > > ---------------------------(end of
broadcast)---------------------------
> > > TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org
> > >
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>