Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004

    Unanswered: Single Query to Update and Append

    I need to write a single query that will append the values from one table into another table but I need to update a single field in the table with a static value. What I'm working with is an Access .adp with an SQL 2000 backend. The database is used to track ticket sales, payments, and contact info for season ticket holders. Prior to upsizing, each year the database would be copied and then choice bits modified for the next year.

    In other words, the history was in several databases, i.e. db2001, db2002, db2003 and for the current year dbcurrent. I decided to create a historic tables in the current DB rather than have umpteen DBs on my SQL server.

    My problem is that I need to create a query that appends to my table the previous years data and then updates the season field to reflect the season that the data came from.

    In short, say I have a table named accounts
    with fields
    account, customer, addr1, addr2, ..., ticket type
    and I want it to end up in
    account_hist with fields
    account, customer, addr1, addr2, ..., ticket type, SEASON
    and make the season equal say 8 which represents the 8th season the team has played.
    I can get both queries to work seperately , but for end user ease, I want to perform both actions at the same time .
    Can anybody point me in the right direction?

  2. #2
    Join Date
    Apr 2004
    Kansas City, MO
    Is there a reason you just can't:

    INSERT account_hist(account,customer,addr1,addr2,ticket_t ype,SEASON)
    SELECT account,customer,addr1,addr2,ticket_type,'8'
    FROM account

    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    May 2004
    Thanks Derrick,
    I was trying to make it more complicated than it really was.

Posting Permissions

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