[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

update specific rows and fields

I've got a series of fields that need to be updated:
  citypairs,pic,sic,tailno,flightlog,noreceiptsattached,nofuelcards
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?
0
fwstealer
Asked:
fwstealer
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
Scott PletcherSenior DBACommented:
No problem doing all the UPDATEs at once, like so:


UPDATE table
SET
    citypairs = pic,
    pic = sic,
    sic = tailno,
    tailno = flightlog,
    flightlog = noreceiptsattached,
    noreceiptsattached = nofuelcards,
    nofuelcards = null
WHERE
    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.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now