469,307 Members | 2,676 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,307 developers. It's quick & easy.

SQL Query

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
7 915
deric
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
1,134 Expert 1GB
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
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
1,134 Expert 1GB
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
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
1,134 Expert 1GB
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
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.

Similar topics

2 posts views Thread by jaysonsch | last post: by
9 posts views Thread by netpurpose | last post: by
3 posts views Thread by Harvey | last post: by
4 posts views Thread by Diamondback | last post: by
14 posts views Thread by Dave Thomas | last post: by
4 posts views Thread by Stan | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.