Best way to update a column with the value from another column in the same record?

Question

Post reply

Best way to update a column with the value from another column in the same record?

Gillian_Pappas2002

SSCommitted

Points: 1714
More actions
January 9, 2014 at 6:36 am

#281927

Hi everyone.
I have a temp table that has 2 date fields, pdate and odate. I want to update the temp table to have
pdate = odate if pdate < odate. They are on the same record and my code doesn't seem to work.
Thanks in advance for all of your help.
G

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

-- Always check first

SELECT pdate, odate FROM #Temp WHERE pdate < odate

-- Then run your update

UPDATE #Temp SET pdate = odate WHERE pdate < odate

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gillian_Pappas2002 SSCommitted Points: 1714 More actions · Answer 2

Thank you so very much Chris for your help. You are awesome.

G

Gillian_Pappas2002 SSCommitted Points: 1714 More actions · Answer 3

Hi Chris.

I have another question. The update works great, thank you, but is there anyway I can use no echo or something so that this result isn't returned to sql, because I have to use this in a BIDS report and it will mess that one up?

Thanks very, very much again.

G

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 4

The select statement that Chris gives is for you to see the data before updating once you are comfortable with the query you can remove the select statement as it is not necessary. If you want to turn off the number of rows affected by the update statement you can use SET NOCOUNT ON.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Gillian_Pappas2002 SSCommitted Points: 1714 More actions · Answer 5

Hi Keith.

Thanks for the information. I have to keep that update in my stored procedure and have it run when BIDs runs. But it will make the Bids hiccup if I can't stop the one line being displayed by the stored procedure. I have tried Set Echo Off, but it will not compile in SQL Server 2008 R2. I've attached the code which is highlighted that I want to have not displayed. Then wish for following code (after the highlight), to be displayed.

Thanks very much Keith.

G

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 6

Keith Tate (1/9/2014)
The select statement that Chris gives is for you to see the data before updating once you are comfortable with the query you can remove the select statement as it is not necessary. If you want to turn off the number of rows affected by the update statement you can use SET NOCOUNT ON.

Same answer applies. Just delete the select statement. It is not needed for the update statement to work:UPDATE #Temp SET pdate = odate WHERE pdate < odate

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Gillian_Pappas2002 SSCommitted Points: 1714 More actions · Answer 7

Thank you Keith. You're awsome. Now I can build my BIDS without hiccups. Thanks so very, very much.

G