Update Error

[simian336]

Oracle is not my forte... Anybody see why this should not work.

Trying to add a value converted to text to a existing text field.

update c
set list = list + cast(b.SERVICE_ID as varchar(12))
from md.subscribe_service a
join md.service_menu b
on a.SERVICE_MENU_ID=b.SERVICE_MENU_ID
join mjlist c
on c.CUSTOMER_ID=a.CUSTOMER_ID
where b.SERVICE_ID=103;

ERROR at line 3:
ORA-00933: SQL command not properly ended

Thanks

Simi

 

[Beilstwh]

update x
set x.list =
(select x.list||to_char(b.service_id)
from md.subscribe_service a
join md.service_menu b
on a.SERVICE_MENU_ID=b.SERVICE_MENU_ID
join mjlist c
on c.CUSTOMER_ID=a.CUSTOMER_ID
where b.SERVICE_ID=103);

Bill
Lead Application Developer
New York State, USA

 

[simian336]

Humm... that did not work. Do I need to convert the rest of the updated table names to x

update x
set x.list =
(select x.list||to_char(b.service_id)
from md.subscribe_service a
join md.service_menu b
on a.SERVICE_MENU_ID=b.SERVICE_MENU_ID
join mjlist x
on x.CUSTOMER_ID=a.CUSTOMER_ID
where b.SERVICE_ID=103);

Thanks

Simi

 

[Beilstwh]

No. You used the alias C for two different tables the x table and the mjlist. WHich one is the correct table for the C alias?

Bill
Lead Application Developer
New York State, USA

 

[simian336]

Just in case someone finds this thread...

Here was my final soltion.

update mjlist c
set list = list || (select to_char(b.service_id)
from md.subscribe_service a
join md.service_menu b
on a.SERVICE_MENU_ID=b.SERVICE_MENU_ID
where b.SERVICE_ID in (181,182,321,322,382,701,702,901,902,903,904,922)
and c.CUSTOMER_ID=a.CUSTOMER_ID);

Simi

 

[LKBrwnDBA]

To avoid updating ALL rows regardless if the sub-query returns a service id or null, do this:

UPDATE Mjlist C
   SET List    =  List 
               || ( SELECT TO_CHAR ( B.Service_Id )
                     FROM Md.Subscribe_Service A
                     JOIN Md.Service_Menu B
                       ON A.Service_Menu_Id = B.Service_Menu_Id
                    WHERE B.Service_Id IN (181,182,321,322,382,701,702,901,902,903,904,922)
                      AND C.Customer_Id = A.Customer_Id )
 WHERE EXISTS 
     ( SELECT '?'
         FROM Md.Subscribe_Service A
         JOIN Md.Service_Menu B
           ON A.Service_Menu_Id = B.Service_Menu_Id
        WHERE B.Service_Id IN (181,182,321,322,382,701,702,901,902,903,904,922)
          AND C.Customer_Id = A.Customer_Id );

Or you can try an inline view update:

UPDATE ( SELECT C.Customer_Id C_Cust_Id
              , C.List C_List
              , C.List || TO_CHAR ( B.Service_Id ) New_List
           FROM Mjlist C
             JOIN Md.Subscribe_Service A 
               ON A.Customer_Id = C.Customer_Id
             JOIN Md.Service_Menu B
               ON B.Service_Menu_Id = A.Service_Menu_Id
              AND B.Service_Id IN (181, 182, 321, 322, 382, 701, 702, 901, 902, 903, 904, 922))
   SET C_List       = New_List;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb