Update Query

21 Oct 2005 12:07 AM

JoseM

Please help!

I want to run an update query to match the service price (serprice) of a
specified service code ("1") from another ("2") in the same table.

Here is a sample that doesn't work.

UPDATE t1 ;
SET t1.serprice = t2.serprice ;
FROM sertable t1 ;
JOIN sertable t2 ;
ON t1.customer = t2.customer ;
AND t1.sercode = "1" ;
AND t2.sercode = "2"

Any help will be greatly appreciated!

21 Oct 2005 12:19 AM

Andre

Try this:

UPDATE t1
SET serprice = t2.serprice
FROM t1 INNER JOIN t2
ON t1.customer = t2.customer
WHERE t1.sercode = 1 AND t2.sercode = 2

Show quote

"JoseM" wrote:

> Please help!
>
> I want to run an update query to match the service price (serprice) of a
> specified service code ("1") from another ("2") in the same table.
>
> Here is a sample that doesn't work.
>
> UPDATE t1 ;
> SET t1.serprice = t2.serprice ;
> FROM sertable t1 ;
> JOIN sertable t2 ;
> ON t1.customer = t2.customer ;
> AND t1.sercode = "1" ;
> AND t2.sercode = "2"
>
> Any help will be greatly appreciated!
>

21 Oct 2005 12:51 AM

JoseM

Thanks Andre,

Didn't work. But I think the problem is the syntax as I am trying to update
a Visual FoxPro table not a SQL server one. Thanks anyways.

Show quote

"Andre" wrote:

> Try this:
>
> UPDATE t1
> SET serprice = t2.serprice
> FROM t1 INNER JOIN t2
> ON t1.customer = t2.customer
> WHERE t1.sercode = 1 AND t2.sercode = 2
>
> "JoseM" wrote:
>
> > Please help!
> >
> > I want to run an update query to match the service price (serprice) of a
> > specified service code ("1") from another ("2") in the same table.
> >
> > Here is a sample that doesn't work.
> >
> > UPDATE t1 ;
> > SET t1.serprice = t2.serprice ;
> > FROM sertable t1 ;
> > JOIN sertable t2 ;
> > ON t1.customer = t2.customer ;
> > AND t1.sercode = "1" ;
> > AND t2.sercode = "2"
> >
> > Any help will be greatly appreciated!
> >

21 Oct 2005 1:38 AM

--CELKO--

First of all an UPDATE is a statement and not a query. This is a
fundamental concept.

>> I want to run an update query to match the service price (serprice) of a specified service code ("1") from another ("2") in the same table. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

21 Oct 2005 1:59 AM

JoseM

Celko,

Thanks for your clarification, I knew that already, it's just that I
normally call everything a "query" -big mistake; and I had to program a
little bit but finally I did what I wanted. Thanks anyways.

Show quote

"--CELKO--" wrote:

> First of all an UPDATE is a statement and not a query. This is a
> fundamental concept.
>
> >> I want to run an update query to match the service price (serprice) of a specified service code ("1") from another ("2") in the same table. <<
>
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
>
>