Solved

How to update with a group count from another table?

Posted on 2008-10-08
12
678 Views
Last Modified: 2013-12-07
I have two tables - PERMIT_SUMMARY and WELL.DWIGHT_SCOUT. I need to update the column PRMT1W in the former table with the count(PRMTDATE) from the latter table that grouped by a substring of UWI with records for the past 7 days. The common field in these two tables are SCID and substr(UWI,1,5). Heres what I have so far. Without where scid in& the result never came back.

update permit_summary b
set prmt1w =
(select count(prmtdate) from well.dwight_scout
where prmtdate > to_char(sysdate - 7, 'yyyymmdd')
and substr(uwi,1,5) = b.scid
group by substr(uwi, 1, 5))
where scid in &
;

Thanks!
0
Comment
Question by:eduliu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
12 Comments
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22673297
OK, let's see about derived table syntax ...

update permit_summary b
Inner Join (Select count(prmtdate) as countDate, 
  substr(uwi, 1, 5) as FK
  from well.dwight_scout
  Group by substr(uwi, 1, 5)) A
on b.SCID = A.FK
Set prmt1w = A.countDate

Open in new window

0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22673547
Hold on ... I saw this in the MySQL zone ... but now I see it's also in PL/SQL.

What DB are you using?
0
 

Author Comment

by:eduliu
ID: 22673566
Oracle. Thanks!
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22673686
So ... does the code I posted work or error out?  Basic SQL syntax is the same between the DB's ... but this has pushed a bit beyond basic :-)
0
 

Author Comment

by:eduliu
ID: 22673705
I'm getting the following error:
ORA-00971: missing SET keyword
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22677262
Seeing something similar here ... http://forums.databasejournal.com/showthread.php?t=42650

Your syntax looks right (with the possible exception of the WHERE scid IN & on which I can't comment).

You say it never came back ... How many rows are in each table?  That's an inefficient join that uses the substring ... but if the tables are relatively small, you should get by with it.
0
 

Author Comment

by:eduliu
ID: 22678087
permit_summary has 3,219 rows with scid column indexed. well.dwight_scout has 515,836 rows with uwi indexed. In the following example, if I specify specific scid, the result came back in seconds. But if I keep as it is, nothing came back after 10min. My questions are two:
1. Is there a way to speed up this, to limit full table scan, etc?
2. Will PL/SQL work better here? Can you provide some hint?

Thanks!

update  permit_summary b
set prmt1w =
(select count(*) from well.dwight_scout
where prmtdate > to_char(sysdate - 7, 'yyyymmdd')
and substr(uwi,1,5) = b.scid
group by substr(uwi, 1, 5))
where b.scid in (select distinct scid from permit_summary);
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22678350
I'm in over my head on the Oracle thing.  I thought it was MySQL when I jumped in.  Sorry.

Please click the Request Attention link and ask that the MySQL zone be removed and the Oracle zone be added.

That should get some Oracle / PL SQL gurus looking at it.
0
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 22678830
Probably function-based index would help
create index x_dwight_scout_01 on well.dwight_scout (substr(uwi,1,5))
update  permit_summary b
set prmt1w =
(select count(*) from well.dwight_scout
where prmtdate > to_char(sysdate - 7, 'yyyymmdd') -- why to store date as a string?
and substr(uwi,1,5) = b.scid -- this makes index on uwi unusable
group by substr(uwi, 1, 5)) -- this is not necessary
 
Maybe you should do that more like
 
update  permit_summary b
set prmt1w =
(select count(*) from well.dwight_scout
where prmtdate > sysdate - 7 -- make prmtdate DATE datatype and additionally you can index it 
and substr(uwi,1,5) = b.scid -- for this function-based index on substr(uwi, 1, 5) would work
)

Open in new window

0
 

Author Comment

by:eduliu
ID: 22679221
The creating index on substr(uwi,1,5) helped the most. Using my original sql, the result came back in 6 sec. A couple of questions from your advice:
- group by substr(uwi, 1, 5) is necessary, as the count has to be rolled up and added together based on substr(uwi,1,5)? Otherwise, I'm getting a single count.
- prmtdate was created by someone before me. They want to capture a format like 20081009. Is there an alternative?

Thanks!
0
 
LVL 14

Accepted Solution

by:
GGuzdziol earned 125 total points
ID: 22679842
Again, you do not need group by. If it was needed, your query would fail with ,,single row subquery returns more then one row'' ;-) Strictly: you are filtering on certain substr(uwi, 1, 5) - grouping by on the same cannot change anything, since there already is only one! That's because subquery is logically (and probably also physically) evaluated for each row of the table being updated. This will do no harm, but makes your code less readable.

Alternative is putting it explicitly as DATE using TO_DATE conversion while inserting. Another option - if you *have to* insert is as string and can do nothing about it - could be setting NLS parameter on session level to use YYYYMMDD as date format (NLS_DATE_FORMAT - or something similiar - I do not remember by heart, and do not have time to check right now).
0
 

Author Closing Comment

by:eduliu
ID: 31504429
Thanks!
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
migrating to phpbb forum from vBulletin 4.2 3 91
How do I partition this table on date? 5 49
Oracle Partitions. 1 14
What does "Between" mean? 6 38
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question