If there are many fields you'll need this instead:
UPDATE mytable m
SET DATE1 = (SELECT DATE1
FROM mytable t
WHERE m.REFNO = t.REFNO
AND DATE1 IS NOT NULL)
WHERE DATE1 IS NULL
I'm not sure if you can alias a table in an UPDATE statement, but I think
you can
Bryan Love ACP
Internet Application Developer
[EMAIL PROTECTED]
-----Original Message-----
From: Cruz, Joseph [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 25, 2001 4:38 PM
To: CF-Talk
Subject: RE: SQL UPDATE
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
