Efficient 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 2005 Forums

Transact-SQL (2005)

Efficient UPDATE?

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2010-02-12 : 09:48:27

I have two tables:

Address
-------
AddressNo int,
HouseName varchar(30),
NoStreet varchar(30),
TownVillage varchar(30),
PostTown varchar(30),
County varchar(30),
PostCode varchar(12)

TempAddress:

AddressNo int,
TempHouseName varchar(30),
TempNoStreet varchar(30),
TempTownVillage varchar(30),
TempPostTown varchar(30),
TempCounty varchar(30),
TempPostCode varchar(12)

Is there an effient way to UPDATE the Address table
and SET the field contents to the Temp...... field contents
if the Temp... field length > 0 and Temp.... field contents are
different from the Address field(s) please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 09:53:08

[code]
UPDATE a
a.HouseName = t.TempHouseName,
a.NoStreet = t.TempNoStreet
... other fields
FROM Address a
JOIN TempAddress t
ON t.AddressNo =a.AddressNo
WHERE a.HouseName <> t.TempHouseName
OR a.NoStreet <> t.TempNoStreet
...
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-12 : 10:01:06

[code]WHERE a.HouseName <> t.TempHouseName
OR a.NoStreet <> t.TempNoStreet[/code]

May need to handle NULLs

[code]
WHERE (a.HouseName <> t.TempHouseName
OR (a.HouseName IS NULL AND t.TempHouseName IS NOT NULL)
OR (a.HouseName IS ~~BIT~~ NOT NULL AND t.TempHouseName IS NULL))
OR (a.NoStreet <> t.TempNoStreet
OR (a.NoStreet IS NULL AND t.TempNoStreet IS NOT NULL)
OR (a.NoStreet IS NOT NULL AND t.TempNoStreet IS NULL))
...
[/code]
or use ANSI NULLS I suppose <shudder!>

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2010-02-12 : 10:01:10

Many thanks for that.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 10:05:58

quote:

Originally posted by Kristen

WHERE a.HouseName <> t.TempHouseName
OR a.NoStreet <> t.TempNoStreet

May need to handle NULLs


WHERE (a.HouseName <> t.TempHouseName
          OR (a.HouseName  IS NULL AND t.TempHouseName IS NOT NULL)
          OR (a.HouseName  IS BIT NULL AND t.TempHouseName IS NULL))
OR (a.NoStreet <> t.TempNoStreet
          OR (a.NoStreet IS NULL AND t.TempNoStreet IS NOT NULL)
          OR (a.NoStreet IS NOT NULL AND t.TempNoStreet IS NULL))
...

or use ANSI NULLS I suppose <shudder!>

do we need to update when t.TempHouseName IS NULL
I dont think OP needs that as per below

UPDATE the Address table
and SET the field contents to the Temp...... field contents
if the Temp... field length > 0 and Temp.... field contents are
different from the Address field(s)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-12 : 10:08:44

Glad you liked my concept of a column being "a bit Null"

When addresses get changed sometimes that means removing a line ... but, yeah, the spec doesn't include that scenario 'tis true.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-12 : 10:11:10

quote:
Originally posted by Kristen

Glad you liked my concept of a column being "a bit Null"

When addresses get changed sometimes that means removing a line ... but, yeah, the spec doesn't include that scenario 'tis true.

Saw it only now

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources