update specific rows and fields

Posted on 2012-08-23
Last Modified: 2012-08-24
I've got a series of fields that need to be updated:
and shift the data over to the left by 1 for rows with id range between:
   2541 - 2548
   532 - 2538
and set nofuelcards as null

i did this sql query - worked for one row
update table set citypairs = pic from table where id = 2540
update table set pic = sic from table where id = 2540
update table set sic = tailno from table where id = 2540
update table set tailno = flightlog from table where id = 2540
update table set flightlog = noreceiptsattached from table where id = 2540
update table set noreceiptsattached = nofuelcards from table where id = 2540
update table set nofuelcards = null from table where id = 2540

how would i get it to go through a series of specified rows?
Question by:fwstealer
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    update table set citypairs = pic from table where id between  2541 and 2548

    if you are updating from the same table, you can do all these updates in the same statement

    update table set citypairs = pic ,
    sic = tailno,
    tailno = flightlog
    from table
    where id between  2541 and 2548
    LVL 68

    Accepted Solution

    No problem doing all the UPDATEs at once, like so:

    UPDATE table
        citypairs = pic,
        pic = sic,
        sic = tailno,
        tailno = flightlog,
        flightlog = noreceiptsattached,
        noreceiptsattached = nofuelcards,
        nofuelcards = null
        id BETWEEN 532 AND 2538 OR
        id BETWEEN 2541 AND 2548

    The original column values are still present until the UPDATE completes, each SET column is NOT done immediately as SQL comes to it.
    That is, even if "pic = sic" were specified as the first SET, "citypairs = pic" would still citypairs to the *original* value of pic, not to the value of sic.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Suggested Solutions

    Title # Comments Views Activity
    What is the best way to calculate hours worked 5 41
    SQL Question 9 33
    SQL User Mappings error 3 32
    sql sproc 2 22
    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now