Re: SQL Update Script.... please help!

In article <974217737.27449.0.nnrp-09.c2d92da9_at_news.demon.co.uk>,   "Darren Lambert" <darrenlambert_at_hotmail.com> wrote:
> Hello
>
> I'm new to Oracle, but have spent a lot of time with Sybase, SQL
 Server,
> etc...
>
> When I try to run the script below I get a ORA-00933 'SQL Command not
> properly ended'.
>
> Fogive me, but this seems a properly formatted statement to me..
>
> Any help is gratefully received.
>
> Thanks
>
> Darren
>
> ------------------------------------------
>
> Update CustomerOrdersH
>
> Set CustomerOrdersH.Deliveryjourneyid = Journeys.journeyid,
> CustomerOrdersH.DeliveryVanNo = Journeys.VanNo,
> CustomerOrdersH.DeliveryRouteID = Journeys.RouteID,
> CustomerOrdersH.DeliverySalesmanPIN = Journeys.SalesmanPIN
>
> From Journeys
>
> Where Journeys.JourneyID = 1210000101
>
> ----------------------------------------------
> Hi,

A couple things to consider:
Oracle syntax and T-SQL syntax differ in situations like this. You seem to want to select from the Journeys table and use the values to set columns in CustomerOrdersH. There are quite a few ways to go on this..

So...here's one of many..you could also create a procedure and accept values as input parameters, or use a cursor if your select will return more than one row. Here it looks like you want to return one row only so,

DECLARE -- need variables to hold select statement values

v_JourneyId   journeys.journeyid%type;
v_vanNo       journeys.VanNo%type;
v_RouteId     journeys.RouteId%type;
v_SalesmanPIN journeys.SalesmanPIN%type;

BEGIN
SELECT JourneyId,VanNo,RouteId,SalesmanPIN INTO v_JourneyId,v_vanNo,v_RouteId,v_SalesmanPIN FROM Journeys
WHERE JourneyId = 1210000101;

UPDATE CustomerOrdersH

Set Deliveryjourneyid = v_JourneyId,
> DeliveryVanNo = v_vanNo,
> DeliveryRouteID = v_RouteId,
> DeliverySalesmanPIN = v_SalesmanPIN;

END;
/
Notice there are terminators (semicolons) after each command (similar to 'go' in tsql)..

Anyway, I hope this gets you started.
Steve

>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Nov 14 2000 - 11:35:38 CST