Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update Statement Creates Unwanted Nulls Expand / Collapse
Author
Message
Posted Tuesday, January 19, 2010 2:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 18, 2011 1:20 PM
Points: 12, Visits: 10
Let's say xTest has 9 records and no nulls. xUpdate has 6 records. I am updating locations for 6 people. After I execute this statement, the remaining 3 records in xTest end up with nulls in location:

update xTest
set location = (select [location] from xUpdate
where xTest.name = xUpdate.name)

Why am I getting nulls? I don't get nulls if my xUpdate file is the full dataset with the correct values.
Post #850130
Posted Tuesday, January 19, 2010 3:22 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
Could you please post the DDL (CREATE TABLE statements) for the two tables? Also, how are the tables related?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #850137
Posted Tuesday, January 19, 2010 3:33 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
Also, give this code a try in a test environment.

update dbo.xTest set
location = xu.location
from
dbo.xTest xt
inner join dbo.xUpdate xu
on (xt.name = xu.name);




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #850141
Posted Tuesday, January 19, 2010 3:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 18, 2011 1:20 PM
Points: 12, Visits: 10
I needed an inner join statement and forgot it. problem solved. thanks for jumping in anyway.
Post #850150
Posted Tuesday, January 19, 2010 3:59 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 18, 2011 1:20 PM
Points: 12, Visits: 10
Lynn, you were right-on with the inner join. thanks.
Post #850151
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse