ansaurus

Question

How can I do this in SQL in a Single Statement?

Answer 1

+9 A:

In standard SQL (there may be better ways in vendor-specific implementations but I tend to prefer standard stuff where possible):

insert into mytable (
    Version, Yr_Varient, Period, CoA, Company, Item, Mvt, Ptnr_Co, Investee,
    FY, GC, LC
) select
    Version, Yr_Varient, Period, CoA, Company, Item, Mvt, Ptnr_Co, Investee,
    2011, GC*1.1, LC*1.1
    from mytable
    where Mvt = 60200
    -- and FY = 2010

You may also want to limit your select statement a little more depending on the results of your testing, such as uncommenting the and FY = 2010 line above to stop copying all your 2009 and 2008 data as well, if any. I asume you only wanted to carry forward the previous year's stuff with a 10% increase on GC and LC.

The way this works is to run the select which gives modified data for FY, GC and LC as per your request, and pump all those rows back into the insert.

paxdiablo 2009-11-11 06:57:07

On some DBMS, adding "and FY = 2010" is an absolute requirement. otherwise the statement would cause an endless loop of inserts (until the disk gets full or some other limit of table size reached).

Milan Babuškov 2009-11-11 07:02:31

Answer 2

A:

  insert into mytable (
    Version,Yr_Varient,FY,Period,CoA,Company,Item,Mvt,Ptnr_Co,Investee,GC,LC)
    SELECT Version ,Yr_Varient,"2011" as FY, Period, CoA, Company , Item , Mvt ,Ptnr_Co ,   Investee , GC*1.1 as GC, LC*1.1 as LC FROM <table Name> 
WHERE Mvt = 60200

Treby 2009-11-11 06:58:00

Answer 3

A:

INSERT INTO _table_ 
    (Version, 
    Yr_Varient, 
    FY, 
    Period, 
    CoA, 
    Company, 
    Item, 
    Mvt, 
    Ptnr_Co,
    Investee,
    GC, 
    LC)
SELECT
    Version,
    Yr_Varient,
    2011,
    Period,
    CoA,
    Company,
    Item,
    Mvt,
    Ptnr_Co,
    Investee,
    GC * 1.1,
    LC * 1.1
FROM
    _table_
WHERE
    Mvt = 60200
    AND FY <> 2011

This statement should work in any SQL-Database.

Edit: Too slow

Pesse 2009-11-11 07:01:06

ansaurus

tags:

views:

answers:

How can I do this in SQL in a Single Statement?

related questions