| Selecting a column not in group by clause [message #619741] |
Thu, 24 July 2014 14:49  |
 |
Member2014
Messages: 33
Registered: July 2014
Location: USA
|
Member |
|
|
Hi All,
I have a requirement to update a column "flg" in a table if there are duplicates based on certain condition.
Condition is that: There should not be any duplicated for the combination of columns address, name, age, title
If there are then I need to make the flg as Y
The query I have is
MERGE INTO tmp_table t
USING
(SELECT rowid rid, address, name, age, title, count(*)
FROM tmp_table
WHERE address is NOT NULL
GROUP BY address, name, age, title
HAVING count(*) > 1
)
ON (p1.rowid = rid)
WHEN MATCHED THEN
p1.flg = 'Y';
The select clause is throwing an error that rowid is not in the GROUP BY clause. If i do not have the rowid (or the ID columns which is the PK on the temp_table)
I will not know what value to merge/ update the value into.
Can anyone help me as to how I can handle this issue?
Thank you in advance.
|
|
|
|
| Re: Selecting a column not in group by clause [message #619743 is a reply to message #619741] |
Thu, 24 July 2014 15:06  |
 |
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
USING (
select rowid rid
from tmp_table
where (address, name, age, title) in
(SELECT address, name, age, title
FROM tmp_table
WHERE address is NOT NULL
GROUP BY address, name, age, title
HAVING count(*) > 1
)
)
[Updated on: Thu, 24 July 2014 15:07] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
