Thread: Table name specified more than once

Hello,

I am having troubles with the following query

  UPDATE "public"."Workstations" SET "Last_Access" = "c1"."Last_Access",
"First_Access" = "c1"."First_Access", "Hits" = "C1"."Hits" FROM
"public"."Workstations" INNER JOIN (select min("Access_Time") as
"First_Access", max("Access_Time") as "Last_Access", count(*) AS "Hits",
"Workstation_Id" FROM "public"."Connections" GROUP BY "Workstation_Id") C1
on "public"."Workstations"."Workstation_Id" = "c1"."Workstation_Id"

It results in a "Table name "Workstations" specified more than once" error
message. Can anyone help me with correcting the syntax?

Thank you,
Alexander


Get the latest news on SurfControl and our products, subscribe to our
monthly e-newsletter, SurfAdvisory at:
http://surfcontrol.us-hosts.com/sc/subscribe

****************************************************************************
The information in this email is confidential and may be legally privileged.
It is intended solely for the addressee. Access to this email by anyone else
is unauthorized. If you are not the intended recipient, any disclosure,
copying, distribution or any action taken or omitted to  be taken in
reliance on it, is prohibited and may be unlawful. If you believe that you
have received this email in error, please contact the sender.
****************************************************************************

On Fri, 13 Jun 2003 17:07:28 +0200, Alexander Mueller
<Alexander.Mueller@SurfControl.com> wrote:
>It results in a "Table name "Workstations" specified more than once" error
>message. Can anyone help me with correcting the syntax?

Don't include the table you want to update in the FROM list:

    UPDATE onetable
       SET col1 = othertable.cola, col2 = othertable.colb
      FROM othertable
     WHERE onetable.x = othertable.y;

In your case othertable is the subselect and the JOIN condition moves
to the WHERE clause.

>copying, distribution or any action taken or omitted to  be taken in
>reliance on it, is prohibited and may be unlawful.

Am I going to be arrested? :-(

Servus
 Manfred