update with join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

update with join

Author

Topic

aden
Starting Member

23 Posts

Posted - 2006-09-22 : 18:19:37

how to update with join?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-09-22 : 18:43:59

UPDATE A SET myCol=B.myCol
FROM myTable A
INNER JOIN myOtherTable B ON A.ID=B.ID

Note the aliases used for each table (A and B), while not always required it's extremely difficult to remember if you don't use them. You also update the alias, not the table that's being aliased. You can also join a table to itself using different aliases, but make sure you are updating the correct one.

There are more syntax examples in Books Online under "UPDATE".

aden
Starting Member

23 Posts

Posted - 2006-09-22 : 18:56:54

update contact2
set contact2.udelivry = '01'
from contact
inner join conthist on ccontact2.accountno = conthist.accountno
where lasdate = '09/15/2005'

what's wrong with this?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-09-22 : 18:58:19

Please read what I posted about aliases.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-23 : 01:47:43

[code]
update contact2
set contact2.udelivry = '01'
from contact2 --I guess it should be contact2
inner join conthist on ccontact2.accountno = conthist.accountno
where lasdate = '09/15/2005'
[/code]

Chirag

aden
Starting Member

23 Posts

Posted - 2006-09-25 : 12:16:29

still not working...:(

are there other way?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-25 : 12:29:47

quote:
Originally posted by aden

still not working...:(

are there other way?

a) What is not working ?
b) Nothing updated ?
c) Updated worngly ?
d) Error message ?

aden
Starting Member

23 Posts

Posted - 2006-09-25 : 12:59:33

the table didn't update...

i tried reading sql for dummies and found that this statement can do the updating..

update contact2
set contact2.udelivery = '09/15/2005'
where accountno in
(select accountno
from conthist
where lastdate = '09/15/2005')

thanks fro your help and time guys!!!

aden

aden
Starting Member

23 Posts

Posted - 2006-09-25 : 14:34:03

how can i set the value of contact.udelivry to be equal to the value of ondate which is on the conthist table?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-09-25 : 16:12:56

Can you post the code that is "not working?" The syntax I gave you before was fine. The following should do the job:

UPDATE A
SET udelivery = B.ondate
FROM contact2 A INNER JOIN conthist B ON A.accountno=B.accountno
WHERE B.lastdate = '09/15/2005'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-25 : 19:31:55

Does the column lasdate contains date & time ?

if does use


where lastdate >= '20050915'
and   lastdate <  '20050916'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources