| how to update sql using distinct select statement [message #6467] |
Sat, 19 April 2003 00:32  |
mohd munawar
Messages: 2
Registered: January 2003
|
Junior Member |
|
|
The first sql statement is
select b1.INVOICE_NUMBER, b1.SERVICE_CODE, b1.NET_AMT
from taj.clm_breakdown3 b1
where portal_trans_id = 36558
--and b1.net_amt != 0
and exists (select 'x'
from taj.clm_breakdown3 b2
where b1.portal_trans_id = b2.portal_trans_id
and b1.service_code = b2.service_code
and (b1.net_amt + b2.net_amt) = 0)
ORDER BY b1.INVOICE_NUMBER, b1.SERVICE_CODE, b1.net_amt
it return 23 records
INVOICE_NUMBER SERVICE_CODE NET_AMT
--------------- --------------- ----------
3622577 1201479 -27.6
3622577 1201479 27.6
3622577 1201479 27.6
3622577 1201479 27.6
3622577 1201940 -40.48
3622577 1201940 40.48
3622577 1201940 40.48
3622577 120253 -8.28
3622577 120253 8.28
3622577 1202650 0
3622577 1202806 -39.47
3622577 1202806 39.47
3622577 1202806 39.47
3622577 1203684 -18.4
3622577 1203684 18.4
3622577 1203684 18.4
3622577 1203684 18.4
3622577 120428 -11.87
3622577 120428 11.87
3622577 120428 11.87
3622577 14047 0
3622577 14066 0
3622577 9991 0
Same sql using distinct
select distinct b1.INVOICE_NUMBER, b1.SERVICE_CODE, b1.NET_AMT
from taj.clm_breakdown3 b1
where portal_trans_id = 36558
--and b1.net_amt != 0
and exists (select 'x'
from taj.clm_breakdown3 b2
where b1.portal_trans_id = b2.portal_trans_id
and b1.service_code = b2.service_code
and (b1.net_amt + b2.net_amt) = 0)
ORDER BY b1.INVOICE_NUMBER, b1.SERVICE_CODE, b1.net_amt
it returns 16 records
INVOICE_NUMBER SERVICE_CODE NET_AMT
--------------- --------------- ----------
3622577 1201479 -27.6
3622577 1201479 27.6
3622577 1201940 -40.48
3622577 1201940 40.48
3622577 120253 -8.28
3622577 120253 8.28
3622577 1202650 0
3622577 1202806 -39.47
3622577 1202806 39.47
3622577 1203684 -18.4
3622577 1203684 18.4
3622577 120428 -11.87
3622577 120428 11.87
3622577 14047 0
3622577 14066 0
3622577 9991 0
how do i update the 16 records instead of 23 records i used key preserve but the query has distinct in it
|
|
|
|
| Re: how to update sql using distinct select statement [message #6471 is a reply to message #6467] |
Sat, 19 April 2003 16:38  |
Scott Mackey
Messages: 46
Registered: February 2003
|
Member |
|
|
Your question doesn't make sense. The query with distinct does not somehow represent 16 rows. It is just combining multiple rows into one row in some cases. In those cases, which record do you want to update and why? You have three records with
3622577 1201479 27.6
Which one of those three should be updated?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
