SQL UPDATE - 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

SQL Server Development (2000)

SQL UPDATE

Author

Topic

oahu9872
Posting Yak Master

112 Posts

Posted - 2005-09-13 : 15:54:18

I am using the following statement to update a table based on the outcome of data in a second table.

UPDATE TblRegistration
SET TempReg = 'NO'
WHERE
TblRegistration.Email IN
(
SELECT tblSubs.Email
FROM tblSubs
WHERE tblSubs.Email IS NOT NULL
)
AND TempReg = 'YES'

What this does is look in tblRegistration, and where the column TempReg = NO and Email matches an existing email in TblSubs, then TempReg is set to Yes. This part works fine.

My problem is I want a column in tblRegistration called CurrentSubID to also get updated to match the value of tblSubs.SubscriberID. I tried the following code...

SET TempReg = 'NO', TblRegistration.CurrentSubID = tblSubs.SubscriberID (2nd line from above)

I got this error message . .. . (The column prefix 'tblSubs' does not match with a table name or alias name used in the query.)

tblSubs does exist and is used throughout the code, so I'm a little confused why it is not working.

So what I'm trying to do is that if there is a certain condition between two tables that is met, data is transferred from one to another. Is this possible?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-13 : 15:58:18

Try this in a test environment:

UPDATE r
SET TempReg = 'NO', CurrentSubID = s.SubscriberID
FROM tblRegistration
INNER JOIN tblSubs s
ON r.Email = s.Email
WHERE s.Email IS NOT NULL AND r.TempReg = 'YES'

Tara

oahu9872
Posting Yak Master

112 Posts

Posted - 2005-09-13 : 16:14:38

Tara, that works great. Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources