update table one field.

ismael

Good morning i have this problem.

I want make an update and get a error.

My example :

update table1 set f1=1 from table1 ,table2
where table1 .f2=table2.f2
and table1 .f3 =table2.f3
and table1 .f4=table2.f4
and table1 .f5=table2.f5

My error is
[Vertica]VJDBC ERROR: Table name "table1 specified more than once [SQL State=42712, DB Errorcode=4901]

Thanks for all.

ismael

solved .
Thanks

Jim_Knicely

Remove the table1 from your FROM clause...

dbadmin=> select * from table1;
 f1 | f2 | f3 | f4 | f5
----+----+----+----+----
  5 |  5 |  5 |  5 |  5
  6 |  6 |  6 |  6 |  6
(2 rows)

dbadmin=> select * from table2;
 f1 | f2 | f3 | f4 | f5
----+----+----+----+----
  5 |  5 |  5 |  5 |  5
(1 row)

dbadmin=> update table1 set f1 = 1 from table2
dbadmin-> where table1.f2 = table2.f2
dbadmin-> and table1.f3 = table2.f3
dbadmin-> and table1.f4 = table2.f4
dbadmin-> and table1.f5 = table2.f5;
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from table1;
 f1 | f2 | f3 | f4 | f5
----+----+----+----+----
  6 |  6 |  6 |  6 |  6
  1 |  5 |  5 |  5 |  5
(2 rows)

ismael

Thanks jim