[TriLUG] Any SQL gurus willing to help?

[Andrew Perrin]

Well no error that time, but I don't think the results are right...

ncsboe=# update attempts set final=true WHERE phone_num IN (SELECT 
DISTINCT phone_num FROM attempts)
ncsboe-#  AND attempt_when=(SELECT max(attempt_when) FROM attempts a2
ncsboe(# WHERE a2.phone_num=phone_num);
UPDATE 6



thanks anyway-
Andy

----------------------------------------------------------------------
Andrew J Perrin - andrew_perrin (at) unc.edu - http://perrin.socsci.unc.edu
Associate Professor of Sociology; Book Review Editor, _Social Forces_
University of North Carolina - CB#3210, Chapel Hill, NC 27599-3210 USA



On Mon, 17 Dec 2007, William Sutton wrote:

> try taking out 'a1' and 'a1.'
>
> if that doesn't work, you need a guru, not a part-timer like me ;)
>
> William Sutton
>
>
> On Mon, 17 Dec 2007, Andrew Perrin wrote:
>
>>
>>> see if this works:
>>>
>>> UPDATE attempts a1 SET final=true
>>>   WHERE phone_num IN (SELECT DISTINCT phone_num FROM attempts)
>>>     AND attempt_when=(SELECT max(attemt_when) FROM attempts a2
>>>                         WHERE a2.phone_num=a1.phone_num)
>>>
>>> (not sure this will work since I haven't actually tested it .... :) )
>>
>> Nice idea, but:
>>
>> ncsboe=# UPDATE attempts a1 SET final=true
>> ncsboe-#    WHERE phone_num IN (SELECT DISTINCT phone_num FROM attempts)
>> ncsboe-#      AND attempt_when=(SELECT max(attemt_when) FROM attempts a2
>> ncsboe(#                          WHERE a2.phone_num=a1.phone_num)
>> ncsboe-# ;
>> ERROR:  syntax error at or near "a1" at character 17
>> LINE 1: UPDATE attempts a1 SET final=true
>>                         ^
>>
>> thanks-
>> Andy
>>
>>
>>
>> ----------------------------------------------------------------------
>> Andrew J Perrin - andrew_perrin (at) unc.edu - http://perrin.socsci.unc.edu
>> Associate Professor of Sociology; Book Review Editor, _Social Forces_
>> University of North Carolina - CB#3210, Chapel Hill, NC 27599-3210 USA
>>
>>
>>
>> --
>> TriLUG mailing list        : http://www.trilug.org/mailman/listinfo/trilug
>> TriLUG Organizational FAQ  : http://trilug.org/faq/
>> TriLUG Member Services FAQ : http://members.trilug.org/services_faq/
>>
>>
> -- 
> TriLUG mailing list        : http://www.trilug.org/mailman/listinfo/trilug
> TriLUG Organizational FAQ  : http://trilug.org/faq/
> TriLUG Member Services FAQ : http://members.trilug.org/services_faq/
>