select query inside the update query??

Question

Consider the tables..

TABLE_1
OPTION VARCHAR2 (2),
NAME VARCHAR2 (50),
SQLSOURCE VARCAHR2(400)

TABLE_2
USER_NAME VARCHAR2 (50),
USER_ID NUMBER (10)
DEPT_ID NUMBER (10)

This is the update stmt..

UPDATE table1 SET OPTION='K',
SQLSOURCE='SELECT DEPT_ID FROM TABLE_2 USER_ID= 15 ORDER BY USER_NAME' 
WHERE NAME='GURU'

Its working fine, but I dont want that hard coded value 15 in the query..

The following query will replace that 15..

SELECT GROUP_ID FROM TABLE_3 WHERE UPPER(GROUP_NAME)='GROUP1'

so.. i tried like this..

UPDATE table1 SET OPTION='K',
SQLSOURCE='SELECT DEPT_ID FROM TABLE_2 USER_ID='||(SELECT GROUP_ID FROM TABLE_3 WHERE UPPER(GROUP_NAME)='GROUP1')  ORDER BY USER_NAME
WHERE NAME='GURU'

it's showing an error..

can anyone tell me the way to do this???

Answer 1

I think you want:

UPDATE table1 
SET OPTION='K'
,   SQLSOURCE='SELECT DEPT_ID FROM TABLE_2 USER_ID='
           ||(SELECT GROUP_ID FROM TABLE_3 WHERE UPPER(GROUP_NAME)='GROUP1')
           ||' ORDER BY USER_NAME'
WHERE NAME='GURU'

Answer 2

This is probably what you want:

UPDATE table_1
   SET "OPTION"  = 'K',
       SQLSOURCE = 'SELECT DEPT_ID FROM TABLE_2 USER_ID=' ||
                   (SELECT GROUP_ID 
                      FROM TABLE_3 
                     WHERE UPPER(GROUP_NAME) = 'GROUP1') ||
                   ' ORDER BY USER_NAME'
 WHERE NAME = 'GURU'