UPDATE mytable
SET DATE1 = (SELECT DATE1
FROM mytable
WHERE REFNO = 123
AND ID = 1)
WHERE DATE1 IS NULL
That should do it for you. As long as the subquery returns only one row, this will
work.
HTH!
:)
Joe
> -----Original Message-----
> From: Adrian Cesana [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, April 25, 2001 6:24 PM
> To: CF-Talk
> Subject: SQL UPDATE
>
>
> I have kind of a strange update I need to run and cant figure
> out how to
> write the thing properly, I think Im making it more difficult
> than it is,
> maybe not.
>
> Here is what the data basically looks like
>
> ID REFNO DATE1
> 1 123 04/25/2001
> 2 123 <null>
> 3 123 <null>
>
>
>
> I need to update the NULL DATE1 fields with the VALUE of
> DATE1 where NOT
> NULL and WHERE REFNO equals REFNO.
>
> I guess it would be something like this but what value do I
> use for the SET?
> Can I use a sub query perhaps, how?
>
>
> UPDATE mytable SET DATE1 = ??? WHERE REFNO = REFNO
>
>
> Thanks,Adrian
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
