May 29, 2014 at 11:33 am
I have a situation where I want to update a column if and only if it is null.
UPDATE Employee
SET VEmployeeID = CASE WHEN E.VEmployeeID IS NULL
THEN ves.VEmployeeID
END
FROM Employee E
INNER JOIN VEmployeeStaging VES
ON E.EID= VES.EID
But what happens is when I run the procedure every other time I run it, it changes everything to null. The other times it puts the VEmployeeID in.
So what is happening is the times when it is not null (where it is not supposed to do anything) it puts a null in. The next time it works.
How do I get it to say don't do anything if something is there already?
Thanks,
Tom
May 29, 2014 at 11:47 am
tshad (5/29/2014)
I have a situation where I want to update a column if and only if it is null.
UPDATE Employee
SET VEmployeeID = CASE WHEN E.VEmployeeID IS NULL
THEN ves.VEmployeeID
END
FROM Employee E
INNER JOIN VEmployeeStaging VES
ON E.EID= VES.EID
But what happens is when I run the procedure every other time I run it, it changes everything to null. The other times it puts the VEmployeeID in.
So what is happening is the times when it is not null (where it is not supposed to do anything) it puts a null in. The next time it works.
How do I get it to say don't do anything if something is there already?
Thanks,
Tom
Like this?
UPDATE E
SET VEmployeeID = ves.VEmployeeID
FROM Employee E
INNER JOIN VEmployeeStaging VES ON E.EID = VES.EID
where E.VEmployeeID IS NULL
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 29, 2014 at 11:54 am
tshad (5/29/2014)
I have a situation where I want to update a column if and only if it is null.
UPDATE Employee
SET VEmployeeID = CASE WHEN E.VEmployeeID IS NULL
THEN ves.VEmployeeID
END
FROM Employee E
INNER JOIN VEmployeeStaging VES
ON E.EID= VES.EID
But what happens is when I run the procedure every other time I run it, it changes everything to null. The other times it puts the VEmployeeID in.
So what is happening is the times when it is not null (where it is not supposed to do anything) it puts a null in. The next time it works.
How do I get it to say don't do anything if something is there already?
Thanks,
Tom
The flaw is in the case statement, when no conditions are met, it will return null. Add an ELSE paragraph in the statement supplying the existing ID or simply follow Sean's suggestion on filtering the set.
😎
May 29, 2014 at 12:07 pm
My code does two very important things. First, it only updates the rows that need to be updated instead of updating them all. Secondly it references the alias in the update statement instead of the table. There are times when the engine can get confused. IIRC it is when the table is referenced more than once in the FROM portion of the update.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 29, 2014 at 12:18 pm
Sean Lange (5/29/2014)
My code does two very important things. First, it only updates the rows that need to be updated instead of updating them all. Secondly it references the alias in the update statement instead of the table. There are times when the engine can get confused. IIRC it is when the table is referenced more than once in the FROM portion of the update.
My bad here, should have said: Add an ELSE paragraph in the statement supplying the existing ID or better, follow Sean's suggestion on filtering the set.
😎
May 29, 2014 at 12:23 pm
Eirikur Eiriksson (5/29/2014)
Sean Lange (5/29/2014)
My code does two very important things. First, it only updates the rows that need to be updated instead of updating them all. Secondly it references the alias in the update statement instead of the table. There are times when the engine can get confused. IIRC it is when the table is referenced more than once in the FROM portion of the update.My bad here, should have said: Add an ELSE paragraph in the statement supplying the existing ID or better, follow Sean's suggestion on filtering the set.
😎
No no your comment made perfect sense to me. I was just trying to elaborate a little bit on what I suggested and why. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 29, 2014 at 1:45 pm
Both Erik and Sean had the solutions I was looking for.
Thanks,
Tom
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply