updating dup row

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&gt;, 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