[TriLUG] Any SQL gurus willing to help?

[William Sutton]

right tool for the job.  right now the problem is you need to update a 
table based on the contents of that table (the attempts table).  The 
safest approach is to write a program that will iterate over a set of rows 
(e.g., by distinct phone_num for the max attempt_when values) and update 
only those rows.  It's a lot easier to do programatically (e.g., Perl) 
than with SQL, and more likely to be correct.


William Sutton


On Mon, 17 Dec 2007, Andrew Perrin wrote:

> On Mon, 17 Dec 2007, William Sutton wrote:
>
>> You could always write a Perl script...
>>
>> William Sutton
>>
>
> Yes, that's always my fallback, but it doesn't seem quite right!
>
> Andy
>
>>
>> On Mon, 17 Dec 2007, Andrew Perrin wrote:
>>
>>> 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/
>>>>
>>> --
>>> 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/
>>
>
>
> ----------------------------------------------------------------------
> 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/
>
>