By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,362 Members | 1,337 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,362 IT Pros & Developers. It's quick & easy.

SQL Query

P: 95
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('sonia.sardana@yahoo.co.in')
insert into mail values('a.xzy@gmmail.com')


create table mail2(email varchar(50))
insert into mail2 values('a.xzy@gmmail.com')
insert into mail2 values('sardana.sonia@gmail.com')
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.
Apr 9 '08 #1
Share this Question
Share on Google+
7 Replies


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

Expand|Select|Wrap|Line Numbers
  1. Update mail set email='X' where mail.email=mail2.email
  2.  
Apr 10 '08 #2

Delerna
Expert 100+
P: 1,134
Yes the basic syntax for update is
Expand|Select|Wrap|Line Numbers
  1. UPDATE NameOfTableToUpdate
  2.   SET NameOfField1ToUpdate=VaueForField1,
  3.       NameOfField2ToUpdate=ValueForField2,
  4.       .......etc
  5.  

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
Apr 10 '08 #3

P: 95
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.
Apr 10 '08 #4

Delerna
Expert 100+
P: 1,134
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
Apr 11 '08 #5

P: 95
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'.
Apr 11 '08 #6

Delerna
Expert 100+
P: 1,134
Expand|Select|Wrap|Line Numbers
  1. UPDATE Mail
  2. SET Email='X'
  3. FROM Mail,Mail2
  4. WHERE Mail.Email=Mail2.Email
  5.  
Apr 11 '08 #7

P: 95
Thx,I
t worked out.
Apr 12 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.