From: | Andreas Kretschmer <akretschmer(at)spamfence(dot)net> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: select from two tables |
Date: | 2008-09-15 12:01:26 |
Message-ID: | 20080915120126.GA9436@tux |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Claus Guttesen <kometen(at)gmail(dot)com> schrieb:
> Hi.
>
> I have two tables, images and duplicates. The images-table is our
> current table and has approx. 90 mill. entries. I want to weed out
> duplicate file-entries (based on the md5-checksum of the file and
> user-id) and update the file name with the first entry found, if any.
>
> The images-table is:
>
> id serial primary key,
> userid int,
> filename text,
> hashcode text,
> and some additional fields like upload-time, exif-date etc.
>
> Duplicates:
> id serial primary key,
> userid int,
> filename text,
> hashcode text,
> ref_count int
>
> What I'd like to do is to perform a single query where I select from
> both tables and then test whether the file is all-ready in duplicates:
I'm not sure if i understand you correctly, but maybe this is what you
want. First, my tables:
test=# select * from images;
userid | filename | ref_count
--------+----------+-----------
1 | foo |
2 | bar |
3 | foobar |
(3 Zeilen)
Zeit: 0,153 ms
test=*# select * from duplicates ;
userid | filename
--------+----------
2 | bar
3 | foobar
3 | foobar
(3 Zeilen)
Okay, now i update images and set the corrent ref_count:
test=*# update images
set ref_count = count from (
select i.userid, i.filename, count(d.filename) from images i
left outer join duplicates d
using(userid,filename)
group by 1,2
) foo
where
images.userid=foo.userid and
images.filename=foo.filename;
UPDATE 3
Zeit: 0,621 ms
test=*# select * from images;
userid | filename | ref_count
--------+----------+-----------
1 | foo | 0
2 | bar | 1
3 | foobar | 2
(3 Zeilen)
HTH, Andreas
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
From | Date | Subject | |
---|---|---|---|
Next Message | Fernando Hevia | 2008-09-15 20:14:25 | Re: Pls Hlp: SQL Problem |
Previous Message | Claus Guttesen | 2008-09-15 10:04:55 | select from two tables |