Help with Update suggestion.. - 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 suggestion..

Author

Topic

danielc
Starting Member

49 Posts

Posted - 2006-08-14 : 16:55:15

Hello All,

I have written the following query and would like to condense it to a shorter update query:

USE db1
GO

UPDATE stu
SET ln =
CASE
WHEN StudentLastName <> '' AND SchoolCode = SC
THEN StudentLastName
ELSE ln
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET fn =
CASE
WHEN StudentFirstName <> '' AND SchoolCode = SC
THEN StudentFirstName
ELSE fn
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET mn =
CASE
WHEN StudentMiddleName <> '' AND SchoolCode = SC
THEN StudentMiddleName
ELSE mn
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET ad =
CASE
WHEN MailAddress <> '' AND SchoolCode = SC
THEN MailAddress
ELSE ad
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET cy =
CASE
WHEN mailcity <> '' AND SchoolCode = SC
THEN mailcity
ELSE cy
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET st =
CASE
WHEN mailstate <> '' AND SchoolCode = SC
THEN mailstate
ELSE st
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET zc =
CASE
WHEN mailzip <> '' AND SchoolCode = SC
THEN mailzip
ELSE zc
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET rad =
CASE
WHEN resaddress <> '' AND SchoolCode = SC
THEN resaddress
ELSE rad
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET rcy =
CASE
WHEN rescity <> '' AND SchoolCode = SC
THEN rescity
ELSE rcy
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET rst =
CASE
WHEN resstate <> '' AND SchoolCode = SC
THEN resstate
ELSE rst
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET rzc =
CASE
WHEN reszip <> '' AND SchoolCode = SC
THEN reszip
ELSE rzc
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET tl =
CASE
WHEN homephone <> '' AND SchoolCode = SC
THEN homephone
ELSE tl
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET bd =
CASE
WHEN studentdob <> '' AND SchoolCode = SC
THEN studentdob
ELSE bd
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET sx =
CASE
WHEN Gender <> '' AND SchoolCode = SC
THEN Gender
ELSE sx
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET ec =
CASE
WHEN ethnicity1 <> '' AND SchoolCode = SC
THEN ethnicity1
ELSE ec
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET ec2 =
CASE
WHEN ethnicity2 <> '' AND SchoolCode = SC
THEN ethnicity2
ELSE ec2
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET hl =
CASE
WHEN homelang <> '' AND SchoolCode = SC
THEN homelang
ELSE hl
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET bcy =
CASE
WHEN cityborn <> '' AND SchoolCode = SC
THEN cityborn
ELSE bcy
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET bst =
CASE
WHEN stateborn <> '' AND SchoolCode = SC
THEN stateborn
ELSE bst
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET bcu =
CASE
WHEN countryborn <> '' AND SchoolCode = SC
THEN countryborn
ELSE bcu
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

UPDATE stu
SET ped =
CASE
WHEN parentedu <> '' AND SchoolCode = SC
THEN parentedu
ELSE ped
END
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid;

It works, but too long and beleive there could be an alternative to shorten it.

Thank you in advance for your help...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-14 : 17:25:51

Combine them into one query:


UPDATE stu
SET 
	ln =
		CASE
			WHEN StudentLastName <> '' AND SchoolCode = SC THEN StudentLastName
			ELSE ln
		END,
	fn =
		CASE
			WHEN StudentFirstName <> '' AND SchoolCode = SC
				THEN StudentFirstName
			ELSE fn
		END,
...
FROM db1.dbo.STU s JOIN db2.dbo.StudentEnrollment se ON s.id = se.studentid

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources