February 7, 2006 at 6:49 pm
Hello All,
I want to update Table A's two columns if its 3rd column equals a column's data in Table B. How can I do it?
I write like this:
Update TableA
Set Number = 2, Type = 580
where TableA.Email = TableB.2nd_Email
and TableA.Number = 0
It generates errors "the Prefix TableB doesn't match with a table name or alias name"
Can anybody tell me how I can re-write the query?
Then I tried like this:
Update TableA
Set Number = 2, Type = 580
where exists
(Select * from TableB
where TableA.Email = TableB.2nd_Email
and TableA.Number = 0)
Then it shows me another error:
Cannot insert duplicate key row in object 'TableA' with unique index 'User_Fields_Key0'. The statement has been terminated.
What happened inside the database??How can I avoid that duplication?
Thanks a lot in advance for your help or hints!
Henry
February 7, 2006 at 6:58 pm
Update TableA
Set Number = 2, Type = 580
FROM TableB
where TableA.Email = TableB.2nd_Email
and TableA.Number = 0
_____________
Code for TallyGenerator
February 7, 2006 at 7:52 pm
Hello Serqiy,
Thanks a lot! That's what I want to ask!
But it still generates the error same as the 'where exists' clause. I might need to sovle the duplication problem inside my data which doesn't allow me to insert same data into TableA. Do you have any idea how I can check duplication before insertion?
By the way, how can I write a if...then clause in SQL set values clause?
I want to :
Update TableA
Set Type = 580, Number =
(Case 2 where TableA.Email = TableB.2nd_Email and TableA.Number = 0,
Case 3 where TableA.Email = TableB.2nd_Email and TableA.Number = 0)
FROM TableB
I mean if ... then Number=2, if...Then Number=3
Thanks a lot Serqiy,
Have a good day!
Henry
February 7, 2006 at 9:03 pm
What is unique index 'User_Fields_Key0' ?
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply