CODEWITHSUNDEEP
sqloracle-database
bearhunterUA
bearhunterUA

Reputation: 259

WHERE statement with one to many option

I have a query

UPDATE TBL$TEMPLATE_PARAM
   SET NAME='new_name'
   WHERE NAME='old_name' AND FIXED_VALUE='some_value';

I want improve it with one more WHERE expression: AND BEAN_NAME='bean' but BEAN_NAME is a row from other table TBL$TEMPLATE.

Its one to many (one template - many template params). TBL$TEMPLATE_PARAM store TEMPLATE_ID and its a foreign key to ID of TEMPLATE.

What is a best way to write WHERE NAME='old_name' AND FIXED_VALUE='some_value' AND TBL$TEMPLATE.BEAN_NAME='bean'?

Upvotes: 1

Views: 130

Answers (2)

Patrick Bacon
Patrick Bacon

Reputation: 4660

I like Mureinik's solution, but for completeness sake, I figured I would provide a solution which uses a merge statement.

Since we know that there is a many to one relationship between the table being updated,TBL$TEMPLATE_PARAM, and the table being used to filter out records, TBL$TEMPLATE, the merge statement is well suited.

Specifically, as the Oracle documentation states (11g), "You cannot update the same row of the target table multiple times in the same MERGE statement"

Knowing the type of relationship between these tables is essential when using a merge statement.

Here is my alternate solution:

MERGE INTO tbl$template_param ttp USING tbl$template tt ON (ttp.template_id = tt.id )
WHEN MATCHED THEN
  UPDATE
  SET ttp.NAME        ='new_name'
  WHERE 1             = 1
  AND tt.bean_name    = 'bean'
  AND ttp.NAME        = 'old_name'
  AND ttp.fixed_value = 'some_value';

Upvotes: 1

Mureinik
Mureinik

Reputation: 311883

You could use a subquery:

UPDATE TBL$TEMPLATE_PARAM
SET    NAME='new_name'
WHERE  NAME='old_name' AND 
       FIXED_VALUE='some_value' AND
       TBL$TEMPLATE_PARAM.TEMPLATE_ID IN (SELECT ID
                                          FROM   TBL$TEMPLATE
                                          WHERE  BEAN_NAME='bean')

Upvotes: 2

Related Questions