|
View previous topic :: View next topic
|
| Author |
Message |
sreenivasreddyg
New User
Joined: 23 Apr 2005
Posts: 39
Location: delhi
|
|
|
|
| I have a table with many rows for each policy number and the total number of rows are stored in another table. Unfortunately, for some policy numbers the values are not same. Now, I need to find all the policy numbers which are having the unmatched values and update the second table with the correct number (total of all the rows in the first table for each policy). I can do this by an application program but I want an SQL query for this. Can someone help? |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
If i needed to fix some incorrect counts, i would write the program and create an audit trail of the "bad" values and what they were corrected to be.
If you just run thru the tables changing numbers, you may cause yourself later problems if anyone has questions. Of course, i'd suggest a backup of the table to be updated before you begin. |
|
| Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
| Code: |
TABLE POLICY_DETAIL_TAB
-------------------------------------------------
POLICY_NO POLICY_COLUMN1 POLICY_COLUMN2
-------------------------------------------------
1111 A1 A2
1111 B1 B2
1111 C1 C2
2222 D1 D2
2222 E1 E2
3333 F1 F2
------------------------------------------------- |
| Code: |
TABLE POLICY_COUNT_TAB
-------------------------------------------------
POLICY_NO POLICY_COUNT
-------------------------------------------------
1111 3
2222 3
3333 1
------------------------------------------------- |
Okay now let's say you have above mentioned 2 tables and you want to have an SQL that finds out the row about POLICY_NO 2222 since it does not have a proper count on POLICY_COUNT_TAB.
Query below should return you the policy numbers, that needs updated and are currently not correct. Now you may find some flaws in the query, since I didnt run it before posting. But in my hope, it should be a good starting point for you to dig in more.
| Code: |
SELECT B.POLICY_NO
FROM POLICY_COUNT_TAB B,
POLICY_DETAIL_TAB A
WHERE B.POLICY_NO = A.POLICY_NO
AND B.POLICY_COUNT <> ( SELECT COUNT(*)
FROM POLICY_DETAIL_TAB C
GROUP BY C.POLICY_NO
HAVING B.POLICY_NO = c.POLICY_NO); |
Similarly something can be written to update too, but that I dont want to post w/o testing. Anyway, do not forget what Mr. Dick has advised on to take a backup before you run any update, so you always have a smily face at the end.  |
|
| Back to top |
|
|
snehalpatel
New User
Joined: 13 Sep 2007
Posts: 37
Location: India
|
|
|
|
Hi
I tried above query. It gave -815 sqlcode.
| Code: |
SELECT B.POLICY_NO
FROM POLICY_COUNT_TAB B,
POLICY_DETAIL_TAB A
WHERE B.POLICY_NO = A.POLICY_NO
AND B.POLICY_COUNT <> ( SELECT COUNT(*)
FROM POLICY_DETAIL_TAB C
GROUP BY C.POLICY_NO
HAVING B.POLICY_NO = c.POLICY_NO); |
'SQLCODE = -815, ERROR: A GROUP BY OR HAVING CLAUSE IS IMPLICITLY OR EXPLICITLY SPECIFIED IN A SUBSELECT OF A BASIC PREDICATE OR A SET CLAUSE OF AN UPDATE STATEMENT "
THen I remove GROUP BY / Having condition and Instead of those i used Where Clause
| Code: |
SELECT B.POLICY_NO
FROM POLICY_COUNT_TAB B,
POLICY_DETAIL_TAB A
WHERE B.POLICY_NO = A.POLICY_NO
AND B.POLICY_COUNT <> ( SELECT COUNT(*)
FROM POLICY_DETAIL_TAB C
WHERE B.POLICY_NO = c.POLICY_NO); |
The result was
POLICY_NO
2222
2222
NUMBER OF ROWS DISPLAYED IS 2
Can Anybody help to get Unique Rows instead of Duplicates . |
|
| Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
Use disctinct.
| Code: |
SELECT DISTINCT(B.POLICY_NO)
FROM POLICY_COUNT_TAB B,
POLICY_DETAIL_TAB A
WHERE B.POLICY_NO = A.POLICY_NO
AND B.POLICY_COUNT <> ( SELECT COUNT(*)
FROM POLICY_DETAIL_TAB C
WHERE B.POLICY_NO = c.POLICY_NO); |
|
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| The SQL for collecting the data was given to OP a few days ago in another thread. I don't have the SQL for doing the updates. Why maintain a count when it can be quickly determined, it is just something else that can get out sync. |
|
| Back to top |
|
|
snehalpatel
New User
Joined: 13 Sep 2007
Posts: 37
Location: India
|
|
|
|
| priyesh.agrawal wrote: |
Use disctinct.
| Code: |
SELECT DISTINCT(B.POLICY_NO)
FROM POLICY_COUNT_TAB B,
POLICY_DETAIL_TAB A
WHERE B.POLICY_NO = A.POLICY_NO
AND B.POLICY_COUNT <> ( SELECT COUNT(*)
FROM POLICY_DETAIL_TAB C
WHERE B.POLICY_NO = c.POLICY_NO); |
|
Thanks priyesh
The Above query worked giving Unique Row values!!! |
|
| Back to top |
|
|
|
|
