Re: Lookup in Query

UPDATE table1
SET table1.Rec_FW = ( SELECT table2.Fiscal_Week

I don't want the Rec_Date field to be updated. I want the Query to use the
Rec_Date field and compare to the other table and then put the fiscal week
into a field called Rec_FW. I thought the "SET" line in my first query would
have done that, but it's not actually changing the value to the fiscal week.

"Tom Ellison" wrote:

Dear JB:

Sorry, no, it doesn't make sense to me right now. How can you update a
date/time column (Rec_Date) to be a week?

Tom Ellison


"jbgarrett1" <jbgarrett1@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B2434ACB-0887-420F-9F0B-C97C94F9B843@xxxxxxxxxxxxxxxx
Tom,

This is pulling the right record, but I need the query to actually update
Rec_Date to the fiscal week.
Does that make sense?

"Tom Ellison" wrote:

Dear JB:

Let's make it simpler. Test just a SELECT query:

SELECT table1.Rec_FW, table2.Fiscal_Week
FROM table1, table2
WHERE table1.Rec_Date BETWEEN table2.Saturday AND table2.Friday
AND EXISTS
(SELECT table2.Fiscal_Week
FROM table2
WHERE table1.Rec_Date BETWEEN table2.Saturday AND table2.Friday)

Next, does this do anything different:

SELECT table1.Rec_FW, table2.Fiscal_Week
FROM table1, table2
WHERE table1.Rec_Date BETWEEN table2.Saturday AND table2.Friday

If the specific row does not exist, I don't think the latter query would
return that anyway.

I hope this looks like progress. Please let me know what you find.

Tom Ellison


"jbgarrett1" <jbgarrett1@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C6B14F8C-F2BF-439A-ABDB-5C5468334318@xxxxxxxxxxxxxxxx
Sorry to be so vague.
Here is my SQL Statement

UPDATE table1
SET table1.Rec_FW = ( SELECT table2.Fiscal_Week
FROM table2
WHERE table1.Rec_Date BETWEEN table2.Saturday AND table2.Friday)
WHERE EXISTS
( SELECT table2.Fiscal_Week
FROM table2
WHERE table1.Rec_Date BETWEEN table2.Saturday AND table2.Friday);

This is returning the right amount of Records when I run it, but it is
not
actually updating that field. They are just left blank.






.

Relevant Pages

  • Re: Lookup in Query
    ... FROM Table1, Table2 ... You can see the correct FW in this query. ... "Tom Ellison" wrote: ...
    (microsoft.public.access.queries)
  • Re: Lookup in Query
    ... "Tom Ellison" wrote: ... FROM Table1, Table2 ... You can see the correct FW in this query. ...
    (microsoft.public.access.queries)
  • Re: Lookup in Query
    ... This is pulling the right record, but I need the query to actually update ... "Tom Ellison" wrote: ... FROM table1, table2 ...
    (microsoft.public.access.queries)
  • Update statement using multiple tables
    ... I have a question, hopefully an easy one, about updating values in a ... I would like to have a query ... I thought that if i put table2 in the update clause (update table1, ...
    (comp.databases.informix)
  • RE: Update statement using multiple tables
    ... I would like to have a query ... I thought that if i put table2 in the update clause (update table1, ... but then I get a syntax error. ...
    (comp.databases.informix)