ERROR: table name "mytableteste" specified more than Once

Asked

Viewed 44 times

0

to try to do this query to "shuffle" the table data. This same query worked in mysql, but not in postgresql.

always get this error [Code: 0, SQL State: 42712] ERROR: table name "mytableteste" specified more than Once

   UPDATE mytableteste set
  desc_nome = desc_nome.newValue,
  numr_cpf = numr_cpf.newValue,
  data_nascimento = data_nascimento.newValue,
  desc_mae = desc_mae.newValue from mytableteste
                                
  INNER JOIN (
  SELECT ROW_NUMBER() OVER (ORDER BY numg_iddoobjeto) AS rn, numg_iddoobjeto from mytableteste
) AS PKrows ON mytableteste.numg_iddoobjeto = PKrows.numg_iddoobjeto
-- repeat the following JOIN for each column you want to randomize
INNER JOIN (
  SELECT ROW_NUMBER() OVER (ORDER BY random()) AS rn, desc_nome AS newValue  from mytableteste
) AS desc_nome ON PKrows.rn = desc_nome.rn
INNER JOIN (
  SELECT ROW_NUMBER() OVER (ORDER BY random()) AS rn, numr_cpf AS newValue  from mytableteste
) AS numr_cpf ON PKrows.rn = numr_cpf.rn
INNER JOIN (
  SELECT ROW_NUMBER() OVER (ORDER BY random()) AS rn, data_nascimento AS newValue  from mytableteste
) AS data_nascimento ON PKrows.rn = data_nascimento.rn
INNER JOIN (
  SELECT ROW_NUMBER() OVER (ORDER BY random()) AS rn, desc_mae AS newValue  from mytableteste
) AS desc_mae ON PKrows.rn = desc_mae.rn
sql postgresql

1 answer

0

I managed to tidy up here

with subquery as ( select * from mytableteste

INNER JOIN ( SELECT ROW_NUMBER() OVER (ORDER BY numg_iddoobject) AS Rn, numg_iddoobject as numg_iddoobjetoC from mytabletest ) AS Pkrows ON mytableteste.numg_iddoobjeto = Pkrows.numg_iddoobjetoC -- repeat the following JOIN for each column you want to Randomize INNER JOIN ( SELECT ROW_NUMBER() OVER (ORDER BY Random()) AS Rn, desc_nome AS newValueNome from mytableteste ) AS desc_name ON Pkrows.Rn = desc_name.Rn INNER JOIN ( SELECT ROW_NUMBER() OVER (ORDER BY Random()) AS Rn, numr_cpf AS newValueCPF from mytableteste ) AS numr_cpf ON Pkrows.Rn = numr_cpf.Rn INNER JOIN ( SELECT ROW_NUMBER() OVER (ORDER BY Random()) AS Rn, data_nascimento AS newValueData_Nascimento from mytableteste ) AS data_nascimento ON Pkrows.Rn = data_nascimento.Rn INNER JOIN ( SELECT ROW_NUMBER() OVER (ORDER BY Random()) AS Rn, desc_mae AS newValueDesc_mae from mytableteste ) AS desc_mae ON Pkrows.Rn = desc_mae.Rn ) UPDATE mytableteste set desc_name = subquery.newValuenome, numr_cpf = subquery.newValuecpf, data_nascimento = subquery.newValuedata_nascimento, desc_mae = subquery.newValuedesc_mae from subquery Where mytableteste.numg_iddoobjeto = subquery.numg_iddoobjetoc

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.