UPDATE QUERY Using Enterprise Manager (SQL Server)

C

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!
 
Carol said:
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!

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
 
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
 
Back
Top