SQL Query

[sonia.sardana]

Write an update query to join the mail table with a table called mail2 where the email addresses match.Set Email field equal to 'X'.

create table mail(email varchar(50))
insert into mail values('[e-mail]')
insert into mail values('[e-mail]')


create table mail2(email varchar(50))
insert into mail2 values('[e-mail]')
insert into mail2 values('[e-mail]')
select * from mail2

Final Query--
Update mail.email set email='X' where mail.email=mail2.email
Error is coming Invalid object name 'mail.email'. I want to update the entries in Mail Table.

[deric]

Hi..
You need not specify the table's field in the Update statement.
http://msdn2.microsoft.com/en-us/library/aa260662(SQL.80).aspx

Update mail set email='X' where mail.email=mail2.email
 

[Delerna]

Yes the basic syntax for update is

UPDATE NameOfTableToUpdate
  SET NameOfField1ToUpdate=VaueForField1,
      NameOfField2ToUpdate=ValueForField2,
      .......etc
 

so your error is coming from the mail.email in

Update mail.email set email='X' .........

it should be
Update mail set email='X' ........

as pointed out by the previous post

Also the where clause is not correct because the mail2 table is not setup to be used by the query

[sonia.sardana]

Delerna can u tell me what to write in the where clause.
If I write the foll.Query-
Update mail set email='X' where mail.email=mail2.email

Error is there-
The multi-part identifier "mail2.email" could not be bound.

[Delerna]

I am unsure what your intent is here.

What data is in table Mail2 ?
Are you intending that Mail2 has a list of emails that you want to update within the main Mail table.

In other words, out of all the records in Mail only update the ones that exist in Mail2.

Or are you intending to update the email field in the mail table to the email field in the mail2 table.

or is it something else.
The way to write it is dependent on your intent

[sonia.sardana]

Two tables are there Mail & Mail2. I want that if email matches in both the tables then update that all that entries in Mail Table to 'X'.

[Delerna]

UPDATE Mail
SET Email='X'
FROM Mail,Mail2
WHERE Mail.Email=Mail2.Email
 

[sonia.sardana]

Thx,I
t worked out.