Error in Update T-SQL in SQL Server 2005

I want to update some data in EditEmail table from DBUsers database to EditEmail table in DBCurrent datebase.So i got error when i execute following statement:

USE DBCurrent

UPDATE [DBUsers].[dbo].[EditEmail] EN
SET EN.MailSubject = E.MailSubject,
    EN.MailMessage = E.MailMessage
FROM
(
    SELECT * FROM EditEmail
) AS E
WHERE EN.Type = E.Type

Error message:

ERROR开发者_Python百科:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'EN'.
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'AS'.

Is there any syntax mistake in this T-SQL?

You cannot give the table you're specifying in the UPDATE statement a table alias (for whatever reason - don't ask me why this isn't possible, ask the T-SQL design team)...

Try this statement instead:

USE DBCurrent

UPDATE 
    [DBUsers].[dbo].[EditEmail] 
SET 
    MailSubject = E.MailSubject,
    MailMessage = E.MailMessage
FROM
    dbo.EditEmail E
WHERE 
    DBUsers.dbo.EditEmail.Type = E.Type
    AND E.Type = 'blahblah'  -- or whatever additional conditions you have!

You need to specify the table being updated in full, e.g. with its database, schema, table and column name, in the WHERE clause.

There is also no need for your "artificial" subquery there to reference the EditMail table - just define that in the FROM clause and give it a table alias (here, they're supported).

T-SQL doesn't allow to define an alias on the table updated, but it allows to update an alias:

This fixes the syntax, I think the logic must still be improved. Please try (I didn't check):

UPDATE EN
SET EN.MailSubject = E.MailSubject,
    EN.MailMessage = E.MailMessage
FROM [DBUsers].[dbo].[EditEmail] EN
    join EditEmail E
on EN.Type = E.Type