CODEWITHSUNDEEP
sqldb2
user1508454
user1508454

Reputation: 301

Regarding joins and subquery

I have below query that I am using ..

select  * from app_subsys_param where assp_name like '%param_name%'

where param_name is the name of the parameter. From this query we will get the assp_id corresponding to the parameter. With this id we look up into app_subsys_parmval table to get the value of the parameter.

 update app_subsys_parmval  set aspv_value = 'true' where assp_id = id_val

Now instead of separately launching the two sql statements , I want to combime both of them as one is there any sub query or join mechanism that can combine both of them in one statement , please advise

Upvotes: 1

Views: 121

Answers (3)

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11052

Look at the MERGE statement. This is the ANSI SQL:2003 standard for UPDATE … FROM.

Documentation:

  • MERGE for DB2 for Linux/UNIX/Windows
  • MERGE for DB2 z/OS 9.1

Upvotes: 1

lc.
lc.

Reputation: 116538

Use a subselect in your update statement:

UPDATE app_subsys_parmval  
SET aspv_value = 'true' 
WHERE id_val = (SELECT assp_id 
                FROM app_subsys_param 
                WHERE assp_name LIKE '%param_name%')

Note, I am assuming a bit about what's in the * of your select *.

Upvotes: 1

Jonathan Hall
Jonathan Hall

Reputation: 79784

You need to use UPDATE .. FROM syntax:

UPDATE app_subsys_paramval
SET aspv_value = 'true'
FROM app_subsys_param
WHERE app_subsys_param.id = app_subsys_paramval.id
    AND app_subsys_param.value LIKE '%param_name%';

Upvotes: 2

Related Questions