bestimmte Spalten mit zufälligen Werten aus anderen Tabellen updaten

Hallo Postgres Fans

Ich möchte gern Daten in einer Tabelle mit fiktiven zufallswerten füllen. Dabei müssen allerdings die geupdateten werte auch zueinander passen.
Ein Beispiel:

Tabelle 1 (zu ändernde)
hans, wurst, kleckerstrasse, 5, 03045
uta, guckindieluft, ratterdiedumstrasse, 10, 04023
hausi, pausi, klausistrasse, 30, 03402

Tabelle 2 (korrekte adressen)
berliner str, 1a, 03040
klausstrasse, 100, 02245


nun möchte ich quasi in tabelle 1 die strassen, nr und plz updaten mit korrekten tupeln aus tabelle 2 und das zufällig. Es soll also keine Berliner Str.100 geben

Mein erster ansatz wäre das hier gewesen:

UPDATE SET
strasse=n.strasse
nr=n.nr
plz=n.plz
FROM
(SELECT strasse,nr,plz FROM tabelle2 ORDER BY random()) as n
WHERE
???

Tja was nun in die where klausel unten ?

Es geht darum für die entwicklung eine Livedatenbank zu anonymisieren um nicht mit sensiblen daten zu hantieren, aber trotzdem gültige Daten zu haben und vor allem auch die gleiche datenmenge.



test=*# select * from namen;
 name  |  str  | nr         
-------+-------+----        
 name1 | platz | 1
 name2 | platz | 2
 name3 | platz | 3
 name4 | platz | 4
(4 Zeilen)

Zeit: 0,290 ms
test=*# select * from adressen;
     str     | nr
-------------+----
 dorfstr     | 23
 schanzenstr | 6
 bayrische   | 1
 postplatz   | 3
(4 Zeilen)

Zeit: 0,236 ms
test=*# with n as (select *, row_number() over () from namen), a as (select *, row_number() over (order by random()) from adressen) select n.name, a.str, a.nrfrom n left join a on n.row_number=a.row_number;
 name  |     str     | nr
-------+-------------+----
 name1 | bayrische   | 1
 name2 | postplatz   | 3
 name3 | schanzenstr | 6
 name4 | dorfstr     | 23
(4 Zeilen)

Zeit: 0,549 ms
test=*# with n as (select *, row_number() over () from namen), a as (select *, row_number() over (order by random()) from adressen) select n.name, a.str, a.nrfrom n left join a on n.row_number=a.row_number;
 name  |     str     | nr
-------+-------------+----
 name1 | schanzenstr | 6
 name2 | postplatz   | 3
 name3 | bayrische   | 1
 name4 | dorfstr     | 23
(4 Zeilen)

Zeit: 0,468 ms
test=*#

So vielleicht?

Andreas

Ein Problem ist , dass ich in der orginal tabelle 1000e von Einträgen habe. In der Zufalls Adressen Tabelle nur 100. Deswegen hab ich eine Zuordnung über orig.objid%zeilenanzahlZufallstabelle=row_number(zufallstabelle) die zuordnung gemacht. Das funktioniert ja auch ganz nett.

mein 2. Problem: Ich kann mir mit unten stehenden query jetzt die korrekten neuen zeilen zusammen bauen. das ist sehr schön doch ich will ja eigentlich die zeilen updaten. und nicht mir nen query anschauen.

In PG kann man anscheindend nicht mehrere spalten aus ein und dem selben select updaten
dokumentation postgres 8.2:

According to the standard, the column-list syntax should allow a list of columns to be assigned from a single row-valued expression, such as a sub-select:

UPDATE accounts SET (contact_last_name, contact_first_name) =
    (SELECT last_name, first_name FROM salesmen
     WHERE salesmen.id = accounts.sales_id);

[color="red"]This is not currently implemented — the source must be a list of independent expressions.[/color]

Mein query was auch schöne daten liefert. wie update ich jetzt damit ?

with
origin AS (SELECT *, row_number() OVER () FROM personen.anschrift),
zufall AS (SELECT *, row_number() OVER (ORDER BY random()) FROM tmp_adressen)
SELECT origin.objid,zufall.strasse,zufall.hausnummer,zufall.plz,zufall.ort
FROM origin
LEFT JOIN zufall ON (origin.row_number%100+1=zufall.row_number)
LIMIT 100;



test=# select * from namen;
 name  |  str  | nr
-------+-------+----
 name1 | platz | 1
 name2 | platz | 2
 name3 | platz | 3
 name4 | platz | 4
(4 Zeilen)

Zeit: 0,187 ms
test=*# update namen set str=foo.str, nr = foo.nr from (with n as (select *, row_number() over () from namen), a as (select *, row_number() over (order by random()) from adressen) select n.name, a.str, a.nr from n left join a on n.row_number=a.row_number) foo where namen.name=foo.name;
UPDATE 4
Zeit: 0,641 ms
test=*# select * from namen;
 name  |     str     | nr
-------+-------------+----
 name1 | bayrische   | 1
 name2 | schanzenstr | 6
 name3 | dorfstr     | 23
 name4 | postplatz   | 3
(4 Zeilen)

Andreas