Updating a Table by Comparing to another...please help
Hello,
I would like to use SQL/dbaccess to update a table by taking the values from
another.
The query I am trying to run is:
UPDATE tocall
SET fdate=(SELECT DISTINCT fdate FROM temptocall WHERE
temptocall.rx=tocall.rx),
refdue=(SELECT DISTINCT refdate FROM temptocall WHERE
temptocall.rx=tocall.rx)
WHERE rx IN (SELECT rx FROM temptocall)
I keep getting
284: A subquery has returned not exactly one row.
Is there any other approach I can take with this?
Please help.
Thanks,
Michael
Hi!
It looks like you are getting more than one value returned from the
subquery. Try MIN or MAX instead of distinct, IF it makes sense in
your situation.
HTH
Michael
chakraboy wrote:
> Hello,
>
> I would like to use SQL/dbaccess to update a table by taking the values from
> another.
>
> The query I am trying to run is:
>
> UPDATE tocall
> SET fdate=(SELECT DISTINCT fdate FROM temptocall WHERE
> temptocall.rx=tocall.rx),
> refdue=(SELECT DISTINCT refdate FROM temptocall WHERE
> temptocall.rx=tocall.rx)
> WHERE rx IN (SELECT rx FROM temptocall)
>
> I keep getting
>
> 284: A subquery has returned not exactly one row.>
> Is there any other approach I can take with this?
>
> Please help.
>
> Thanks,
> Michael
Hello,
Yes it did help....unfortunately the query I had written changes the dates
globally for rx IN that list. Look at the WHERE statement. Therefore it
does write the correct dates corresponding with the correct rx values.
How do I tell it to match rx and then update that fdate,refdue with the
correct data.
In other words...can I join in an UPDATE statement?
Please Help.
Thanks,
Michael
Michael Krzepkowski <michaelk@sqlcanada.com> wrote in message
news:38D1620F.2A556B00@sqlcanada.com...
> Hi!
>
> It looks like you are getting more than one value returned from the
> subquery. Try MIN or MAX instead of distinct, IF it makes sense in
> your situation.
>
> HTH
>
> Michael
>
>
> chakraboy wrote:
>
> > Hello,
> >
> > I would like to use SQL/dbaccess to update a table by taking the values
from
> > another.
> >
> > The query I am trying to run is:
> >
> > UPDATE tocall
> > SET fdate=(SELECT DISTINCT fdate FROM temptocall WHERE
> > temptocall.rx=tocall.rx),
> > refdue=(SELECT DISTINCT refdate FROM temptocall WHERE
> > temptocall.rx=tocall.rx)
> > WHERE rx IN (SELECT rx FROM temptocall)
> >
> > I keep getting
> >
> > 284: A subquery has returned not exactly one row.> >
> > Is there any other approach I can take with this?
> >
> > Please help.
> >
> > Thanks,
> > Michael
>
Hi Michael,
> UPDATE tocall
> SET fdate=(SELECT DISTINCT fdate FROM temptocall WHERE
> temptocall.rx=tocall.rx),
> refdue=(SELECT DISTINCT refdate FROM temptocall WHERE
> temptocall.rx=tocall.rx)
> WHERE rx IN (SELECT rx FROM temptocall)
> 284: A subquery has returned not exactly one row.DISTINCT does not mean different from the values in tocall! Instead
this statment tries to assign all distinct fdate values from
temptocall to the (single) current row.
> Is there any other approach I can take with this?
I think you have to work with a temporary table where you store all
those fdate which are different in the tables:
SELECT temptocall.rx, temptocall.rx.fdate
FROM tocall, temptocall
WHERE tocall.rx = temptocall.rx
AND tocall.fdate != temptocall.fdate; INTO TEMP tmp_fdate;
In the second step you can update all those rows in tocall.
UPDATE tocall
SET fdate =
(SELECT tmp_fdate.fdate
FROM tmp_fdate
WHERE tmp_fdate.rx = tocall.rx)
WHERE tocall.rx IN (SELECT tmp_fdate.rx FROM tmp_fdate)
Don't forget to drop the temp table afterwards. Then you have to do
the same for your refdate. I you want to avoid a row to be updated
twice when both values have changed, you should collect all rows to be
modified in the temp table first.
Greetings, Marcus.
--
Marcus.Gelleschun, ProSieben Information Service GmbH
Gutenbergstr. 3, D-85767 Unterfoehring, Tel: 089/9507-5149
UPDATE tocall
SET (fdate, refdue) =
(( SELECT MAX(fdate), MAX(refdate)
FROM temptocall
WHERE temptocall.rx = tocall.rx))
WHERE EXISTS (
SELECT 1 FROM temptocall
WHERE temptocall.rx = tocall.rx);
Replace MAX by MIN if you want the earliest date rather than the most recent.
The EXISTS clause would probably be faster than the IN clause if a lot of
matching rows exist in temptocall .
Presumably, you have an index on temptocall(rx). If not, make it (at least,
temporarily, for this statement).
Rudy
chakraboy wrote:
> Hello,
>
> I would like to use SQL/dbaccess to update a table by taking the values from
> another.
>
> The query I am trying to run is:
>
> UPDATE tocall
> SET fdate=(SELECT DISTINCT fdate FROM temptocall WHERE
> temptocall.rx=tocall.rx),
> refdue=(SELECT DISTINCT refdate FROM temptocall WHERE
> temptocall.rx=tocall.rx)
> WHERE rx IN (SELECT rx FROM temptocall)
>
> I keep getting
>
> 284: A subquery has returned not exactly one row.>
> Is there any other approach I can take with this?
>
> Please help.
>
> Thanks,
> Michael