UPDATE QUERY Using Enterprise Manager (SQL Server)

Discussion in 'Database Support' started by Carol, Feb 2, 2004.

  1. Carol

    Carol Guest

    My question is quite simple, I want to do an Update using a table join, and I can't seem to get the Enterprise Manager to allow me to add a second table... What I want to do is the folowing:

    UPDATE A, B
    SET A.SITID = B.SITID
    WHERE A.EN=B.EN;

    In Access, this query works fine. I cannot seem to get the Enterprose Manager to allow me to add a second table.

    Could someone tell me the syntax, please?

    Thanks!
  2. riley

    riley Perch

    Hi,
    I'm not a big sql expert, but here are a couple thoughts.
    Assuming A.SITID is not the primary key, I would remove the ", B" from the UPDATE clause and try something link this:

    UPDATE A
    SET A.SITID = B.SITID
    JOIN B ON B.EN=A.EN;

    If A.SITID is the primary key, you'll have to create something to DELETE the old rows and add new ones with the new SITID.

    Let me repeat, I'm not an sql expert...

    riley
  3. WineIsGood

    WineIsGood Perch

    You must update one table at a time. You can update A while joining it with B to get the values you need.
    ie:
    UPDATE A
    SET A.myField = B.newField
    FROM A, B
    WHERE A.primaryKey = B.foreignKey

Share This Page

JodoHost - 26,000 hosting end-users in 100 countries
Plesk Web Hosting
VPS Hosting
H-Sphere Web Hosting
Other Services