UPDATE QUERY Using Enterprise Manager (SQL Server)

Customer Support Database Support
1

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

[QUOTE=Carol]
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!
[/QUOTE]

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

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