read/compare 1 table then update another

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)

read/compare 1 table then update another

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-01 : 13:38:36

I can easily do this using .Net code behind, but only need to do it once and using sql is better.

I have 3 tables.

tbl1 id(int) and descText(varchar)
tbl2 descText(int) and serial(varchar)
tbl3 descText(varchar) and serial(varchar)

Going by each row, need to:

1. Read serial from tbl2
2. Search tbl3 for serail and get descText
(if not found or null use 0 for id in step 3)
3. Search tbl1 for descText and get id
4. Update tbl2 descText with id found
5. Repeat till end

Anyone have a link for a sample for something like this?

Thanks,

Zath

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-01 : 14:38:04

Nevermind, I went ahead and wrote it on a temp page in .Net

Zath

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-01 : 14:41:08

This should do it:

UPDATE tbl2 SET descText = coalesce(tbl1.[id], 0) 
FROM tbl2
LEFT OUTER JOIN tbl3 ON tbl2.serial = tbl3.serial
LEFT OUTER JOIN tbl1 ON tbl3.descText = tbl1.descText