The reason you are getting multiple records in the subquery is cos you've
removed the table aliases that Brian suggeted. Put them in and you should
be ok if the rest of your data is in the same pattern as the sample dat ayou
posted. i.e.
UPDATE mytable m
SET DATE1 = (SELECT DATE1
FROM mytable t
WHERE m.REFNO = t.REFNO
AND DATE1 IS NOT NULL)
WHERE DATE1 IS NULL
------------------------------------------------------------------
Andrew Ewings
Project Manager
Thoughtbubble Ltd
http://www.thoughtbubble.net
------------------------------------------------------------------
United Kingdom
http://www.thoughtbubble.co.uk/
Tel: +44 (0) 20 7387 8890
------------------------------------------------------------------
New Zealand
http://www.thoughtbubble.co.nz/
Tel: +64 (0) 9 488 9131
------------------------------------------------------------------
The information in this email and in any attachments is confidential and
intended solely for the attention and use of the named addressee(s). Any
views or opinions presented are solely those of the author and do not
necessarily represent those of Thoughtbubble. This information may be
subject to legal, professional or other privilege and further distribution
of it is strictly prohibited without our authority. If you are not the
intended recipient, you are not authorised to disclose, copy, distribute, or
retain this message. Please notify us on +44 (0)207 387 8890.
-----Original Message-----
From: Adrian Cesana [mailto:[EMAIL PROTECTED]]
Sent: 26 April 2001 17:34
To: CF-Talk
Subject: RE: SQL UPDATE
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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
