Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Need help with update syntax using subquery

Re: Need help with update syntax using subquery

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 7 Feb 2007 08:04:47 -0800
Message-ID: <1170864286.992213.100630@l53g2000cwa.googlegroups.com>


On Feb 6, 11:58 am, "Gerard H. Pille" <g..._at_skynet.be> wrote:
> mjohnson wrote:
> > On Feb 5, 5:53 pm, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
>
> >>On Feb 5, 5:24 pm, "mjohnson" <crv..._at_hotmail.com> wrote:
>
> >>>I'm new to PL/SQL and have a TSQL (MSSQL) background. Typically, if I
> >>>needed to do an update using a subquery I would do this:
>
> >>> update mytable set t1.col=t2.col from mytable t1 inner join
> >>>othertable t2 on t1.ID=t2.ID
>
> >>>but that doesn't work in PL/SQL. But this seems to work:
>
> >>> update mytable t1 set (t1.col)=(select col from othertable where
> >>>ID=t1.ID)
>
> >>>Is that the analogous syntax. It seems that this update is taking
> >>>forever -- I would have expected it to return in a matter of seconds
> >>>if not subsecond but it's been 20 minutes and it still hasn't
> >>>completed and there are only 22K records. Is there a better way to do
> >>>this?
>
> >>>thanks for your time!
>
> >>You need a where clause condition on your update statement as you toldOracleto update every row in the table even if there is no match in
> >>the coordinated subquery in which case you just set the column to
> >>NULL.
>
> >>For your query to be efficient you should have an index on
> >>othertable.id
> >>Both tables should have current statistics on them.
> >>If you add an index update the statistics
>
> >>General form
>
> >>update table a
> >>set colx = ( select col_value from table b where b,col = a.col )
> >>where exists ( select 'X' from table b where b.col = a.col )
>
> >>The where clause restricts the set to operating on rows that have a
> >>matching row in table b.
>
> >>Often where an exists clause is used in a statement you can substitute
> >>an IN clause, that is, where col in ( select....
>
> >>In this case I think the exists, with the index, and current
> >>statistics should work fine.
>
> >>Rollback to undo the damage.
>
> >>HTH -- Mark D Powell --- Hide quoted text -
>
> >>- Show quoted text -
>
> > Thanks for the response. I actually do want to update every row in
> > this particular case. I think my problem is with the index on the
> > second table - I'm guessing it doesn't have one. I'm just really
> > surprise that it would take as long as it did for such a small table.
>
> The time needed also depends on the number of records in table a, doesn't it?
>
> If you didn't need to update every record in a, but only those with a relation in b, you could
> try this:
>
> begin
> for r in (select col, col_value from table_b) loop
> update table_a
> set colx = r.col_value
> where col = r.col;
> end loop;
> end;
> /- Hide quoted text -
>
> - Show quoted text -

Because this is a forum post I will state the obvious. for Gerard's solution to be viable table_b should be a small subset of table_a and table_a.col should be indexed otherwise you would have to full scan the larger table_a for every selected row in table_b.

I would compare the above against to be sure that your really need pl/ sql instead of just a single SQL statement.

update table_a
set colx = ( select col_value from table_b where table_b.colj = table_a.col)
where table_a.col in ( select col from table_b [order by col])

HTH -- Mark D Powell -- Received on Wed Feb 07 2007 - 10:04:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US