Updating the data in a table

  • chgn01

    Hall of Fame

    Points: 3535

    Comments posted to this topic are about the item Updating the data in a table

    --------------------------------------
    ;-)“Everything has beauty, but not everyone sees it.” ― Confucius

  • zerko

    SSCommitted

    Points: 1524

    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

    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

    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

    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

    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

    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

    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: 258942

    Nice question, thanks!

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

  • This was removed by the editor as SPAM

  • Thomas Abraham

    SSChampion

    Points: 10761

    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: 104772

    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

    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: 33209

    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: 7053

    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]

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

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