Updating the data in a table

Question

Post reply

Updating the data in a table

chgn01

SSCarpal Tunnel

Points: 4294
More actions
February 27, 2013 at 8:13 pm

#274684

Comments posted to this topic are about the item Updating the data in a table
--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius
February 28, 2013 at 4:08 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 30 total)

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

zerko SSCommitted Points: 1524 More actions · Answer 1

CAUTION: Assumes you have not set your users 'default schema' to point to something other than 'dbo' - otherwise ....

1. table T1 will be created in the default schema,

2. the update statement may or may not fail, depending on if you also have a suitably specced table named T1 in schema 'dbo', ie dbo.T1 exists, and has a column named 'C1'

and the final select statement will return the unaltered contents of table T1 in your default schema.

Lokesh Vij SSChampion Points: 10836 More actions · Answer 2

For me this was an easy one to attempt 🙂

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

demonfox SSCertifiable Points: 6289 More actions · Answer 3

Lokesh Vij (2/27/2013)
For me this was an easy one to attempt 🙂

+1

not so different for me too .. 🙂

thanks for the question 😀

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 4

Good one. :), thank you for posting. (very cute logic...)

i manually iterated the update in mind and 5 OK, 6 OK 7 OK and then for last entry 4 i assumed as 8 and i rushed...:Whistling: and (you know the rest of it...); for 4 max of C1 is not greater than C1 so the value is the same as original.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

demonfox SSCertifiable Points: 6289 More actions · Answer 5

Good one. :), thank you for posting. (very cute logic...)

yes.. and I think , it would be same as this one

DECLARE @maxc INT

SELECT @maxc = MAX(C1)

FROM dbo.T1

UPDATE T1

SET C1 = C1 + @maxc

FROM T1

WHERE C1 < @maxc

a simpler look 😛

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 6

demonfox (2/28/2013)
yes.. and I think , it would be same as this one

...also significant improvement on the EP.

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

demonfox SSCertifiable Points: 6289 More actions · Answer 7

Raghavendra Mudugal (2/28/2013)
demonfox (2/28/2013)
yes.. and I think , it would be same as this one
...also significant improvement on the EP.

+1 .. a better look , should I have said !!! 😉

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 8

Nice question, thanks!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Thomas Abraham SSChampion Points: 10761 More actions · Answer 9

demonfox (2/28/2013)
yes.. and I think , it would be same as this one
... (code removed for brevity) ...
a simpler look 😛

And, while not a great QOTD, this version would be easier on the next person who has to maintain the code.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

TomThomson SSC Guru Points: 104773 More actions · Answer 10

It's a nice simple question. And a good explanation.

However, the reference in the explanation to Rob Conery's adaption of Itzak's description is a bit unfortunate, because he recommends "a simpler mathematical workaround that avoids division altogether" which, since the columns concerned are stated to be integers so that division can lead to rounding, will deliver incorrect results as often as correct ones. For example, with default rounding, if col2 is 7 and col1 is 3, col2>2*col1 is TRUE but col2\col1>2 is FALSE, so the "simpler mathematical workaround" returns rows that the method using CASE does not return.

Also, the method Rob gives for using CASE is far more verbose and complicated than it need be, and that unneccessary complication is the only justification for presenting the non-working "workaround". (I wonder if we need a new word "doesn'tworkaround" for such things?) Here is the simple version:-

SELECT col1, col2

FROM dbo.T1

WHERE CASE WHEN COL1 = 0 THEN NULL ELSE Col2\Col1 END > 2;

All this doesn't really detract from the value of the question, but it certainly does detract from the value of the page referenced.

Tom

db4breakfast Ten Centuries Points: 1097 More actions · Answer 11

I'm sure this question maybe overlooked by those who don't know the "all–at–once operations" property of SQL.

--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.

HanShi SSC-Dedicated Points: 33506 More actions · Answer 12

Easy one 😉

Allthough I got it wrong because I didn't read the whole query :blink: I forgot the " WHERE (SELECT MAX(C1) FROM dbo.T1) > C1" part.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

EL Jerry SSCertifiable Points: 7061 More actions · Answer 13

Great question, I didn't know about all-at-once operations, but was suspecting of it because of weird results from a query I ran once long ago.

Learned something new, again.

Saludos,

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]