From: | John R Pierce <pierce(at)hogranch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: updating dup row |
Date: | 2017-02-17 02:35:39 |
Message-ID: | 8a579838-c767-6ba0-f5df-1ff41c157f84@hogranch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 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 <http://test2.id>, then
> update 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
From | Date | Subject | |
---|---|---|---|
Next Message | James Sewell | 2017-02-17 02:48:30 | Re: PostgreSQL corruption |
Previous Message | Patrick B | 2017-02-17 02:25:57 | updating dup row |