Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79

    Question Unanswered: Update Query for range of dates

    Hi

    I'm looking to update about 80 rows in a table where I need to sum some values from the source table.

    Basically I want to run a query that does this:

    UPDATE tblDaily
    SET SALES = 'x', REDEMPTIONS = 'y', NET = (x + y)
    WHERE FDATE > '13-Feb-2006'
    AND FDATE < '14-Jun-2006'

    The values of 'x' and 'y' are stored in another table, and I can get them using this query:

    SELECT SUM(x), SUM(y), SUM(x + y)
    FROM tblSTAGING
    WHERE FDATE > '13-Feb-2006'
    AND FDATE < '14-Jun-2006'

    What I'm looking to do is to combine these queries so that I can run the UPDATE query with the SELECT query as a sub-query.

    Does anyone know how to do this?

    Thanks in advance!
    Make something idiot proof and someone will make a better idiot...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which database?

    the reason i ask is because joined update syntax varies significantly from one database to another
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    Oracle 10g
    Make something idiot proof and someone will make a better idiot...

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, great

    moving the thread to the oracle forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well based on what you said:
    Code:
    UPDATE tblDaily
    SET (SALES, REDEMPTIONS, NET) = (SELECT SUM(x), SUM(y), SUM(x + y)
                                     FROM tblSTAGING
                                     WHERE FDATE > '13-Feb-2006'
                                     AND FDATE < '14-Jun-2006')
    WHERE FDATE > '13-Feb-2006'
    AND FDATE < '14-Jun-2006';
    But I would imagin you really want to correlate the subquery by some common columns like this:
    Code:
    UPDATE tblDaily
    SET (SALES, REDEMPTIONS, NET) = (SELECT SUM(x), SUM(y), SUM(x + y)
                                     FROM tblSTAGING
                                     WHERE FDATE > '13-Feb-2006'
                                     AND FDATE < '14-Jun-2006'
                                     AND tblSTAGING.some_cols = tblDAILY.some_cols)
    WHERE FDATE > '13-Feb-2006'
    AND FDATE < '14-Jun-2006'
    By the way, you should never use plain character strings where a DATE is expected. Either convert the strings to DATEs explicitly like this:

    TO_DATE('13-Feb-2006','DD-Mon-YYYY')

    ... or use the ANSI standard for DATE literals like this:

    DATE '2006-02-13'

    Finally (and unimportantly) prefixing all table names with a redundant "tbl" is a MS Access habit. We don't do that around here!

Posting Permissions

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