Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Unanswered: Insert / update with the results of group by

    Here is an example of my problem.
    I have two tables
    First one : This is the summary file
    Name tates
    Fields : State char(15)
    : Population int
    Second file : This is the transaction file
    Name : Cities
    Fields : State char(15)
    City char(15)
    Population int

    All day, the users enter information in Cities. At the end of the day, I have a t-sql that read cities,sum all the population by state and update the file State. So far nothing complicated.

    Let say that the first day, there was data for California, Texas and Florida.

    The next day, the users enter information for Maine, Arkansas and California.

    The T-sql will sum the data by state and will need to do two things :
    1. Insert into states for Maine and Arkansas
    2. Update states California

    insert into state (state, population)
    select state, sum(population) from cities group by state

    It works when I insert but it doesn't work when it's update.

    How can I write the SQL Statement without using a cursor ?

    Thanks
    Real

    P.S. I cannot use triggers.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by realdrouin
    It works when I insert but it doesn't work when it's update.
    Well, you posted the INSERT code that works, but not the UPDATE code which you want help with. Fancy posting it?
    I don't suppose you are using SQL Server 2008?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2009
    Posts
    4
    I'm using sql server 2005.

    Can I do the insert and the update in the same statement ? For instance, just a guess

    if exists then update else insert
    select sum(population)...

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by realdrouin
    Can I do the insert and the update in the same statement ?
    'fraid not. The reason I asked if you were using 2008 was because that has the MERGE statement which does just that. If you google "upsert "SQL Server"" you'll find stuff.

    A common way to do this is to create your query and:
    1) JOIN to the base table on the primary key column(s) and update
    2) INSERT into the base table
    Code:
    WHERE NOT EXISTS (SELECT NULL FROM baseTable WHERE baseTable.pk = myQuery.pk)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Also in 2008: Composable DML

    Covers earlier versions too
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •