| Author |
Updating the first row which is null
|
|
| recif20002002@yahoo.fr 2006-04-08, 11:28 am |
| Hello
I am trying to update table1 and set a new value with a condition
selecting only the results for the element with the specified ID and
the first one with the Date field null.
The query executes but it doesn t not update the informations. I ve
tried using TOP 1 and spent hours on this query but i can t get it to
work.
UPDATE table1 SET table1.Name = newName
WHERE table1.Date = (SELECT First(table1.Date) FROM table1 WHERE
table1.Date = Null And table1.ID = specifiedID);;
thank you for your help
Updating the first row which is null
| |
|
| you can use the TOP predicate in a query (and presumably in a subquery,
though i've never tried it), if you also include an ORDER BY clause. the
records have to be in a specified order, so that Access can figure out which
record is the TOP 1.
hth
<recif20002002@yahoo.fr> wrote in message
news:1144508902.675910.60370@e56g2000cwe.googlegroups.com...
> Hello
>
> I am trying to update table1 and set a new value with a condition
> selecting only the results for the element with the specified ID and
> the first one with the Date field null.
> The query executes but it doesn t not update the informations. I ve
> tried using TOP 1 and spent hours on this query but i can t get it to
> work.
>
> UPDATE table1 SET table1.Name = newName
> WHERE table1.Date = (SELECT First(table1.Date) FROM table1 WHERE
> table1.Date = Null And table1.ID = specifiedID);;
>
> thank you for your help
>
> Updating the first row which is null
>
| |
| recif20002002@yahoo.fr 2006-04-08, 11:28 am |
| I tried this but I get an error with this one
UPDATE table1 SET table1.Name = setName
WHERE table1.Date = (SELECT TOP 1 table1.Date FROM table1
WHERE isNull([table1.Date]) = true And table1.ID = specifiedID
ORDER BY DateValue(table1.Date) ASC);
I also tried this but it doesn t update any row
UPDATE table1 SET table1.Name = setName
WHERE table1.ID = specifiedID And table1.Date = (SELECT TOP 1
table1.Date FROM table1
WHERE isNull([table1.Date]) = true);
Any ones has got an idea?
| |
| Bob Quintal 2006-04-08, 11:28 am |
| "recif20002002@yahoo.fr" <recif20002002@yahoo.fr> wrote in
news:1144508902.675910.60370@e56g2000cwe.googlegroups.com:
> Hello
>
> I am trying to update table1 and set a new value with a
> condition selecting only the results for the element with the
> specified ID and the first one with the Date field null.
> The query executes but it doesn t not update the informations.
> I ve tried using TOP 1 and spent hours on this query but i can
> t get it to work.
>
> UPDATE table1 SET table1.Name = newName
> WHERE table1.Date = (SELECT First(table1.Date) FROM table1
> WHERE table1.Date = Null And table1.ID = specifiedID);;
>
> thank you for your help
>
> Updating the first row which is null
>
If table1.date is null, then WHERE table1.Date = (SELECT ....
will never be true, and will not return any recoords.
try
SELECT TOP 1 Date FROM Table1
WHERE table1.Date = Null
AND table1.ID = specifiedID
;
as a query, and use that query in the update
UPDATE table1
SET table1.Name = newName
FROM query1
;
When the two queries work correctly, you can optionally merge
them to get something like
UPDATE table1
SET table1.Name = newName
FROM (SELECT TOP 1 Date FROM Table1
WHERE table1.Date = Null
AND table1.ID = specifiedID
)
;
--
Bob Quintal
PA is y I've altered my email address.
| |
| Bob Quintal 2006-04-08, 11:28 am |
| Bob Quintal <rquintal@sympatico.ca> wrote in
news:Xns979F7A20DCDC
ABQuintal@207.35.177.135:
See my fix, I had an = instead of an IS, which will always be
false.
> "recif20002002@yahoo.fr" <recif20002002@yahoo.fr> wrote in
> news:1144508902.675910.60370@e56g2000cwe.googlegroups.com:
>
>
> If table1.date is null, then WHERE table1.Date = (SELECT ....
> will never be true, and will not return any recoords.
>
> try
SELECT TOP 1 Date FROM Table1
WHERE table1.Date IS Null
AND table1.ID = specifiedID
;
as a query, and use that query in the update
UPDATE table1
SET table1.Name = newName
FROM query1
;
When the two queries work correctly, you can optionally merge
them to get something like
UPDATE table1
SET table1.Name = newName
FROM (SELECT TOP 1 Date FROM Table1
WHERE table1.Date IS Null
AND table1.ID = specifiedID
)
;
--
Bob Quintal
PA is y I've altered my email address.
| |
| John Welch 2006-04-08, 1:28 pm |
| In your (Select top 1) query, your WHERE clause is limiting your results to
only those records where there is no date.
In other words, you're looking through dateless records to find the most
recent one. It's impossible.
When you say "the first one with the Date field null", you're going to have
to find some other way to determine the "first one" since you obviously
can't use the date field to do that.
<recif20002002@yahoo.fr> wrote in message
news:1144508902.675910.60370@e56g2000cwe.googlegroups.com...
> Hello
>
> I am trying to update table1 and set a new value with a condition
> selecting only the results for the element with the specified ID and
> the first one with the Date field null.
> The query executes but it doesn t not update the informations. I ve
> tried using TOP 1 and spent hours on this query but i can t get it to
> work.
>
> UPDATE table1 SET table1.Name = newName
> WHERE table1.Date = (SELECT First(table1.Date) FROM table1 WHERE
> table1.Date = Null And table1.ID = specifiedID);;
>
> thank you for your help
>
> Updating the first row which is null
>
|
|
|
|