Thanks Joeseph and Brian, Im really close now....whats happening is the
SubQuery is returning more than 1 row which wont work. If I put a
MAX(Date1) it works but I get incorrect results. I get the MAX date for ALL
the REFNO instead of just the single set. Any ideas how to get around that?
I should have included a better data example prior, I modified it below.
ID REFNO DATE1
1 123 04/25/2001
2 123 <null>
3 123 <null>
4 222 04/26/2001
5 222 <null>
6 222 <null>
7 333 04/27/2001
8 333 <null>
So basically I want Null DATE1 for REFNO = 123 to be 04/25/2001 AND the Null
DATE1 for REFNO = 222 to be 04/26/2001.
I have several thousands of records like this and would like to do it in a
single pass, in fact Ill need to run this thing daily.
Im trying this now but get multiple results from the SubQuery:
UPDATE mytable
SET DATE1 = (SELECT DATE1
FROM mytable
WHERE REFNO = REFNO
AND DATE1 IS NOT NULL)
WHERE DATE1 IS NULL
-----Original Message-----
From: Bryan Love [mailto:[EMAIL PROTECTED]]
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
-----Original Message-----
From: Cruz, Joseph [mailto:[EMAIL PROTECTED]]
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.
Joe
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
