January 31, 2013 at 8:22 pm
Comments posted to this topic are about the item Oops! SQL column swap or SQL column clobber?
January 31, 2013 at 8:23 pm
Thanks for the helpful information...
January 31, 2013 at 9:34 pm
Good one Thomas! I was aware that column values will be swapped 🙂
~ Lokesh Vij
Link to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
January 31, 2013 at 11:34 pm
Actually I'm unable to understand the comments and answers properly. Problem is with the word "CLOBBER".
But I'm aware of UPDATE behaviour and I know there won't be any issues in the query.
--
Dineshbabu
Desire to learn new things..
February 1, 2013 at 12:02 am
hi dinesh,
In that update query, while execute set c1 = c2 the c1 column values will be changed, then in that next statement c2 = c1 how it ill work? so c1 column is called as "clobber". i thing so.... if it is wrong please any one clear out my doubt.......
**************
Manik
Go fast as possible
Manik
You cannot get to the top by sitting on your bottom.
February 1, 2013 at 12:02 am
Easy one (thanks Itzik! :-)) to end the week. Thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 1, 2013 at 12:03 am
Great Question. But the link provided has no information specific to this behavior.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 1, 2013 at 12:08 am
Kingston Dhasian (2/1/2013)
Great Question. But the link provided has no information specific to this behavior.
+1
--
Dineshbabu
Desire to learn new things..
February 1, 2013 at 12:41 am
Good Question.
-----------------
Gobikannan
February 1, 2013 at 12:55 am
Dineshbabu (1/31/2013)
Actually I'm unable to understand the comments and answers properly. Problem is with the word "CLOBBER".....
Same here but after checking this http://en.wikipedia.org/wiki/Clobbering, it kind of eased the question to understand.
Like it says c1 = c2 and c2 = c1, if we do the same in programming language like this
Dim a As Integer
Dim b As Integer
a = 1
b = 2
a = b
b = a
in the 5th line a = 2 and 6th line b is not 1 as a is already 2 so here the original value of a which was 1 is no longer in picture, so the b is written to b itself with its original value.
(i guess, i got this write... 😉 )
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
February 1, 2013 at 1:40 am
Lets drive this a little further:
CREATE TABLE dbo.Test(a INT NOT NULL, B INT NOT NULL)
INSERT dbo.Test (a,b) VALUES(1,2)
INSERT dbo.Test (a,b) VALUES(3,4)
GO
DECLARE @a INT = 100
,@b INT = 100;
UPDATE dbo.Test
SET a = @b-2
, b = @a
,@a = a
,@b = b;
GO
SELECT * FROM dbo.Test
GO
DROP TABLE dbo.Test
GO
This also succeeds. And I find this one really nasty with lots of aspects as to why this actually does work.
Best Regards,
Chris Büttner
February 1, 2013 at 1:43 am
Nice question ..
Dineshbabu (1/31/2013)
Actually I'm unable to understand the comments and answers properly. Problem is with the word "CLOBBER".But I'm aware of UPDATE behaviour and I know there won't be any issues in the query.
Wikipedia explains it as : "clobbering a file or computer memory is overwriting its contents."
Explanations states that
will create a temporary copy of the entire column c1 for later use replacing selective rows of column c2, after column c1 is ‘clobbered’
since the update happens using the temporary copy , so swapping happens perfectly.
Nicely explained by raghvendra..
Clobbering , In other words could mean, Swap the drink by making a cocktail....
<<>>
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
February 1, 2013 at 2:22 am
This was removed by the editor as SPAM
February 1, 2013 at 2:27 am
Christian Buettner-167247 (2/1/2013)
Lets drive this a little further:
CREATE TABLE dbo.Test(a INT NOT NULL, B INT NOT NULL)
INSERT dbo.Test (a,b) VALUES(1,2)
INSERT dbo.Test (a,b) VALUES(3,4)
GO
DECLARE @a INT = 100
,@b INT = 100;
UPDATE dbo.Test
SET a = @b-2
, b = @a
,@a = a
,@b = b;
GO
SELECT * FROM dbo.Test
GO
DROP TABLE dbo.Test
GO
This also succeeds. And I find this one really nasty with lots of aspects as to why this actually does work.
I understand that you find it nasty. That's because it IS nasty.
Any variable assignment in an UPDATE statement is nasty, and I will celebrate the day this "feature" is removed from the product.
February 1, 2013 at 3:14 am
Hugo Kornelis (2/1/2013)
This also succeeds. And I find this one really nasty with lots of aspects as to why this actually does work.
I understand that you find it nasty. That's because it IS nasty.
Any variable assignment in an UPDATE statement is nasty, and I will celebrate the day this "feature" is removed from the product.[/quote]
You're not a fan of the old quirky update then? Much as I dislike the obtuseness of it, I like the performance and admire the ingenuity of it. It does seem to provide results orders of magnitude higher than other methods in specific circumstances - running totals say.
http://www.simple-talk.com/sql/performance/writing-efficient-sql-set-based-speed-phreakery/
I had not quite registered the use of 'clobber' for such a circumstance (generally it seems to be an unexpected overwriting that breaks desired behaviour). It's a great word and I shall be looking for chances to wheel it out though :hehe:
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply