Help with UPDATE into table - 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)

Help with UPDATE into table

Author

Topic

wintermute1884
Starting Member

1 Post

Posted - 2004-12-15 : 17:01:00

Hello, I am having real problems updating a table in MS SQL Server 2000. I am trying to update a field in a table with data from another table. They both have a field that contains the same unique data (recid). The update statement I have been working with is:

update emailtable
set email = c.contsuppref
from emailtable e, contsupp c
where (e.recid = c.recid)
;

I am doing something wrong, because I get a 0 rows affected results. Please help, I am more than willing to send whoever answers $10 via paypal.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-12-15 : 17:45:06

You need to use a table alias in your update.

update e
set e.email = c.contsuppref
from emailtable e INNER JOIN contsupp c ON (e.recid = c.recid)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-15 : 18:33:50

That won't affect the result.
There is probably something wrong with the join fields - recid. What datatype are they?

How are you doing the update? I ask because the brackets and ; make me think that access might be involved somewhere.
Before an update it's woryh doing a select to see what will be affected
in query analyser

select *
from emailtable e
join contsupp c
on e.recid = c.recid

If that brings back nothing pick an id that is in both tables and look at the records to see why they aren't joining.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources