Thread: [GENERAL] updating dup row
Hi all,
how can I update a row with newest id from another table if it exists somewhere else?
Example:
table test1
- id (primary key)
- id_user_bill
- clientid
table test2
- item_id
- userid (there are duplicated rows here)
- clientid
- id (primary key)
-- finding the dup records
INSERT INTO test2_results
SELECT
item_id,
useridcount(*) as dup_count,
MAX(id) as recent_id
FROMtest2GROUP BYitem_id,useridHAVING COUNT(*) > 1;
if test1.id_user_bill = test2.id, then
update test1.id_user_bill with test2_results.recent_id
I'm using PG 9.2
Thanks!
Patrick.
Patrick.
On 2/16/2017 6:25 PM, Patrick B wrote:
how can I update a row with newest id from another table if it exists somewhere else?Example:table test1
- id (primary key)
- id_user_bill
- clientid
table test2
- item_id
- userid (there are duplicated rows here)
- clientid
- id (primary key)
-- finding the dup recordsINSERT INTO test2_resultsSELECTitem_id,useridcount(*) as dup_count,MAX(id) as recent_idFROMtest2GROUP BYitem_id,useridHAVING COUNT(*) > 1;if test1.id_user_bill = test2.id, thenupdate test1.id_user_bill with test2_results.recent_id
UPDATE test1 SET test1.id_user_bill = test2_results.recent_id FROM test2_results WHERE test1.id_user_bill = test2_results.item_id;
(at least if I interpret what you're asking correctly, there's some errors there, for instance, there's no such field as test2.id shown, and the schema of test2_results is undefined, too)
'from' works very much like a INNER JOIN, and the WHERE clause has to include the join condition.
-- john r pierce, recycling bits in santa cruz