Update problem - 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 2005 Forums

Transact-SQL (2005)

Update problem

Author

Topic

tota00
Starting Member

10 Posts

Posted - 2009-06-04 : 05:19:23

Hi!

Need the sql-syntax for the folloving:

I have stripped the tables just to make it easier...

Table SmsEvent contains SmsEventId (int, counter), SmsMsgTxt (nvarchar), SmsMsgID (int, allow Null) AND table SmsPreDefinedMsg contains SmsMsgId (int, counter), SmsMsgTxt (nvarchar), SmsMsgTxt2 (nvarchar)

Now I want an Update that puts the correct SmsMsgID in table SmsEvent where the SmsMsgTxt in SmsEvent occurs in table SmsPreDefinedMsg fields SmsMsgTxt OR SmsMsgTxt2

Hope this wasn´t confusing!

Thanks in advance // Thomas

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-04 : 05:22:39

UPDATE se
SET se.SmsMsgId = x.SmsMsgId
FROM SmsEvent AS se
INNER JOIN SmsPreDefinedMsg AS spdm ON se.SmsMsgText IN (spdm.SmsMsgTxt, spdm.SmsMsgTxt2)

E 12°55'05.63"
N 56°04'39.26"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 05:26:47

should be something like this:


update se
set SmsMsgID = sp.SmsMsgID 
from SmsEvent se
join SmsPreDefinedMsg sp
on ltrim(rtrim(se.SmsMsgTxt)) = ltrim(rtrim(sp.SmsMsgTxt)) or ltrim(rtrim(se.SmsMsgTxt)) = ltrim(rtrim(sp.SmsMsgTxt2))

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 05:27:30

again - too late