Home » SQL & PL/SQL » SQL & PL/SQL » Updating from a select
Updating from a select [message #349966] Tue, 23 September 2008 10:57 Go to next message
stry_cat
Messages: 3
Registered: November 2005
Junior Member
Ok I've got this horrible looking select statement:

SELECT parent_id,
      LTRIM(MAX(sys_connect_by_path(element_value, ',')) KEEP (DENSE_RANK LAST
   ORDER BY curr), ',') AS thestring
       FROM
      (SELECT t1.parent_id,
        t1.ordinal,
        t1.element_value,
        row_number() over (partition BY t1.parent_id order by t1.ordinal)    AS curr,
        row_number() over (partition BY t1.parent_id order by t1.ordinal) -1 AS prev
         FROM abc.values_table t1
      LEFT JOIN abc.definitions t2
           ON (t1.parent_prop_id  = t2.primary_id)
        WHERE t2.display_name = 'Zones'
      )
   GROUP BY parent_id START
    WITH curr     = 1 CONNECT BY prev = PRIOR curr
    AND parent_id = PRIOR parent_id
    ) z1
    WHERE object_id = parent_id
   ;


Now what I need to do is update another field in another_table with the value of thestring where parent_id = another_table.object_id

The best I've been able to come up with is
UPDATE abc.another_table
set thefield = (
SELECT parent_id,
      LTRIM(MAX(sys_connect_by_path(element_value, ',')) KEEP (DENSE_RANK LAST
   ORDER BY curr), ',') AS thestring
       FROM
      (SELECT t1.parent_id,
        t1.ordinal,
        t1.element_value,
        row_number() over (partition BY t1.parent_id order by t1.ordinal)    AS curr,
        row_number() over (partition BY t1.parent_id order by t1.ordinal) -1 AS prev
         FROM abc.values_table t1
      LEFT JOIN abc.definitions t2
           ON (t1.parent_prop_id  = t2.primary_id)
        WHERE t2.display_name = 'Zones'
      )
   GROUP BY parent_id START
    WITH curr     = 1 CONNECT BY prev = PRIOR curr
    AND parent_id = PRIOR parent_id
    ) z1
    WHERE object_id = parent_id)

However all this ends up doing is putting the same value into all of the rows. Can anyone help me how to figure out how to get the rest of the where clause in here? If I try to add "WHERE object_id = parent_id" after the last closing parentheses it complains about parent_id being an invalid identifier.

Thanks in advance.
Re: Updating from a select [message #349984 is a reply to message #349966] Tue, 23 September 2008 12:01 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
There are quite a few ways to do things along this line, with likely lots of other posts on the subject. I'm not sure I'm helping by providing this answer. There are definite performance considerations you will want to investigate.

Search and read more about correlated subqueries, the merge statement, and updateable join views.

And when in doubt, reference all columns with their table (or an alias) prefix.

MYDBA@orcl > col targ_val format a10
MYDBA@orcl > col source_val format a10
MYDBA@orcl >
MYDBA@orcl > create table target(id, targ_val)
  2  as select level, 'garbage' from dual connect by level <= 10;

Table created.

MYDBA@orcl >
MYDBA@orcl > create table source(id, source_val)
  2  as select level*2, dbms_random.string('U',5) from dual connect by level <= 5;

Table created.

MYDBA@orcl >
MYDBA@orcl >
MYDBA@orcl > select * from target order by 1;

        ID TARG_VAL
---------- ----------
         1 garbage
         2 garbage
         3 garbage
         4 garbage
         5 garbage
         6 garbage
         7 garbage
         8 garbage
         9 garbage
        10 garbage

10 rows selected.

MYDBA@orcl > select * from source order by 1;

        ID SOURCE_VAL
---------- ----------
         2 CWDRR
         4 QSDGF
         6 WQQRY
         8 TMAOO
        10 UZUJY

5 rows selected.

MYDBA@orcl >
MYDBA@orcl > update target
  2  set targ_val = (select source_val from source a where target.id = a.id)
  3  where target.id in (select source.id from source);

5 rows updated.

MYDBA@orcl >
MYDBA@orcl > select * from target order by 1;

        ID TARG_VAL
---------- ----------
         1 garbage
         2 CWDRR
         3 garbage
         4 QSDGF
         5 garbage
         6 WQQRY
         7 garbage
         8 TMAOO
         9 garbage
        10 UZUJY

10 rows selected.

MYDBA@orcl >
MYDBA@orcl > drop table target;

Table dropped.

MYDBA@orcl > drop table source;

Table dropped.

MYDBA@orcl >
MYDBA@orcl > set echo off


Re: Updating from a select [message #350032 is a reply to message #349966] Tue, 23 September 2008 16:52 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Hey Stry_Cat,
Updates are amongst my least fun queries for this matter; but
I believe you need to qualify the same criteria in the outer
where clause.

For eg, if we had two tables MASTER and DETAIL

MASTER
---------
ID
SERVICE-DATE

DETAIL
--------
ID
SEQ-NO
SERVICE-DATE

Where we wanted to update SERVICE-DATE on the MASTER with
the max(SERVICE-DATE) from the DETAIL line items,

CREATE TABLE MASTER(ID NUMBER(6),SERVICE_DATE DATE);
INSERT INTO MASTER
SELECT LEVEL,NULL FROM DUAL CONNECT BY LEVEL < 11;
COMMIT;

SELECT * FROM MASTER; 

ID     SERVICE_DATE         
1      null                 
2      null                 
3      null                 
4      null                 
5      null                 
6      null                 
7      null                 
8      null                 
9      null                 
10     null                 
 
CREATE TABLE DETAIL(ID NUMBER(6),
                    SEQ_NO NUMBER(2),
                    SERVICE_DATE DATE);
BEGIN
FOR X IN 1 .. 10 LOOP
	INSERT INTO DETAIL(ID,SEQ_NO,SERVICE_DATE)
	VALUES (X,1,SYSDATE - 2);
	
	INSERT INTO DETAIL(ID,SEQ_NO,SERVICE_DATE)
	VALUES(X,2,SYSDATE - 1);
	
	INSERT INTO DETAIL(ID,SEQ_NO,SERVICE_DATE)
	VALUES(X,3,SYSDATE);
	COMMIT;
END LOOP;
END;

SELECT * FROM DETAIL;

ID     SEQ_NO SERVICE_DATE         
1      1      9/21/2008 4:51:10 PM 
1      2      9/22/2008 4:51:10 PM 
1      3      9/23/2008 4:51:10 PM 
2      1      9/21/2008 4:51:10 PM 
2      2      9/22/2008 4:51:10 PM 
2      3      9/23/2008 4:51:10 PM 
3      1      9/21/2008 4:51:10 PM 
3      2      9/22/2008 4:51:10 PM 
3      3      9/23/2008 4:51:10 PM 
4      1      9/21/2008 4:51:10 PM 
4      2      9/22/2008 4:51:10 PM 
4      3      9/23/2008 4:51:10 PM 
5      1      9/21/2008 4:51:10 PM 
5      2      9/22/2008 4:51:10 PM 
5      3      9/23/2008 4:51:10 PM 
6      1      9/21/2008 4:51:10 PM 
6      2      9/22/2008 4:51:10 PM 
6      3      9/23/2008 4:51:10 PM 
7      1      9/21/2008 4:51:10 PM 
7      2      9/22/2008 4:51:10 PM 
7      3      9/23/2008 4:51:10 PM 
8      1      9/21/2008 4:51:10 PM 
8      2      9/22/2008 4:51:10 PM 
8      3      9/23/2008 4:51:10 PM 
9      1      9/21/2008 4:51:10 PM 
9      2      9/22/2008 4:51:10 PM 
9      3      9/23/2008 4:51:10 PM 
10     1      9/21/2008 4:51:10 PM 
10     2      9/22/2008 4:51:10 PM 
10     3      9/23/2008 4:51:10 PM 


We know we want to update each MASTER.SERVICE_DATE with
MASTER.SERVICE_DATE = SELECT MAX(D.SERVICE_DATE)
FROM DETAIL D
WHERE M.ID = D.ID;

And it is that criteria which also defines which rows
we want to update. So the full UPDATE can be defined with
UPDATE MASTER M
   SET M.SERVICE_DATE = (SELECT MAX(D.SERVICE_DATE) 
                           FROM DETAIL D
		           WHERE M.ID = D.ID)
WHERE EXISTS (
	  SELECT 1 FROM DETAIL D 
	     WHERE M.ID  = D.ID
		  );


As well, with...

UPDATE MASTER M
   SET M.SERVICE_DATE = (SELECT MAX(D.SERVICE_DATE) 
                           FROM DETAIL D
	 		  WHERE M.ID = D.ID)
WHERE EXISTS (
	 SELECT MAX(D.SERVICE_DATE) 
                    FROM DETAIL D
	            WHERE M.ID = D.ID 
		  );


Yet the execution pathes are not the same though the output
produced is. That aside, your query obviously has some
more complexities to it so I would verify that the same will
hold true before reducing the EXISTS clause down from
the entire criteria specified in your SET criteria.
Just started doing it and lost my train of thought
(long day!) Embarassed

So, I would start unfortunately with making a WHERE EXISTS clause for the entire criteria specified in the update set
clause.

I dont have sample tables built for your case, but
this is what I intend:

UPDATE abc.another_table  
set  thefield = (
SELECT parent_id,
      LTRIM(MAX(sys_connect_by_path(element_value, ',')) KEEP (DENSE_RANK LAST
   ORDER BY curr), ',') AS thestring
       FROM
      (SELECT t1.parent_id,
        t1.ordinal,
        t1.element_value,
        row_number() over (partition BY t1.parent_id order by t1.ordinal)    AS curr,
        row_number() over (partition BY t1.parent_id order by t1.ordinal) -1 AS prev
         FROM abc.values_table t1
      LEFT JOIN abc.definitions t2
           ON (t1.parent_prop_id  = t2.primary_id)
        WHERE t2.display_name = 'Zones'
      )
   GROUP BY parent_id START
    WITH curr     = 1 CONNECT BY prev = PRIOR curr
    AND parent_id = PRIOR parent_id
    ) z1
    WHERE object_id = parent_id)
WHERE EXISTS   
SELECT parent_id,
      LTRIM(MAX(sys_connect_by_path(element_value, ',')) KEEP (DENSE_RANK LAST
   ORDER BY curr), ',') AS thestring
       FROM
      (SELECT t1.parent_id,
        t1.ordinal,
        t1.element_value,
        row_number() over (partition BY t1.parent_id order by t1.ordinal)    AS curr,
        row_number() over (partition BY t1.parent_id order by t1.ordinal) -1 AS prev
         FROM abc.values_table t1
      LEFT JOIN abc.definitions t2
           ON (t1.parent_prop_id  = t2.primary_id)
        WHERE t2.display_name = 'Zones'
      )
   GROUP BY parent_id START
    WITH curr     = 1 CONNECT BY prev = PRIOR curr
    AND parent_id = PRIOR parent_id
    ) z1
    WHERE object_id = parent_id)


Now I know the analytical functions can be pulled and this
can be simplified to the bare join, just keep verifying
results equal after each decomposition.
And something tells me we're going to have an aliasing prob
above.

Sorry for losing steam mid-post, hopefully I am conveying
the concept - make the WHERE clause for the UPDATE the
same as the criteria within the SET.

Simplify back from there for performance.

If you have some table create scripts for the query tables
I will try to complete this after a nap and a coffee.
As well, I am curious about something in the two explain
plans for the small example UPDATES I want to look
further into and will post on. The extended criteria
version showed something that for now I will say my
eyes decieve me on but merits my own clarification.

Best regards,
Sleepy Harry

p.s. Dont you wish you could just do
UPDATE Table1 T1, Table2 T2
set t1.whatever = t2.whatever
where t1.id = t2.id!





Re: Updating from a select [message #350046 is a reply to message #350032] Tue, 23 September 2008 22:16 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The MERGE statement is the easient way to do this. Search for MERGE in this article

Ross Leishman
Previous Topic: Update, last digit of a number column
Next Topic: Updating Distinct Values
Goto Forum:
  


Current Time: Fri Dec 09 05:59:37 CST 2016

Total time taken to generate the page: 0.10664 seconds