Postgres Pro
Downloads
Home   >   mailing lists

[GENERAL] updating dup row - Mailing list pgsql-general

From Patrick B
Subject [GENERAL] updating dup row
Date
Msg-id CAJNY3ivTvSZm2=9DX8oAbqbvfqcBwKKota2b1w_dv2jpGqRz_g@mail.gmail.com
Whole thread Raw
Responses Re: [GENERAL] updating dup row  (John R Pierce <pierce@hogranch.com>)
List pgsql-general
Tree view
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,
userid
count(*) as dup_count,
MAX(id) as recent_id
FROM
test2
GROUP BY 
item_id,
userid
HAVING 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.

pgsql-general by date:

Previous
From: Richard Brosnahan
Date:
Subject: [GENERAL] PostgreSQL mirroring from RPM install to RPM install-revisited
Next
From: John R Pierce
Date:
Subject: Re: [GENERAL] updating dup row