Win has asked for the wisdom of the Perl Monks concerning the following question:

Can anyone tell me whether I need to use Perl to do the following SQL commands (for MS SQL Server 7). I wish to: 

SELECT DISTINCT Aggregated_area
FROM Geo_postaggregated_pop_count_Ward;

[download]
Take each of these values and then where Aggregated_area = each distinct value do the following: 

UPDATE Geo_postaggregated_pop_count_Ward
SET Sum_male_count =
        (SELECT SUM(G.Person_count)
      FROM Geo_postaggregated_pop_count_Ward G
      WHERE Sex = 1);

UPDATE Geo_postaggregated_pop_count_Ward
      SET Sum_female_count =
              (SELECT SUM(G.Person_count)
            FROM Geo_postaggregated_pop_count_Ward G
            WHERE Sex = 2);

UPDATE Geo_postaggregated_pop_count_Ward
            SET Sum_of_person_count =
                    (SELECT SUM(G.Person_count)
                  FROM Geo_postaggregated_pop_count_Ward G
                  WHERE Sex = 1 OR
                       Sex = 2);

[download]
Do I really need to use the Perl foreach command to do this?

Replies are listed 'Best First'.
Re: SQL or Perl solution
by Abigail-II (Bishop) on Mar 08, 2004 at 15:45 UTC
    Do I really need to use the Perl foreach command to do this?
    What do you mean by need? The commands seem like perfectly valid SQL commands to me, so the obvious answer seems no to me. Clearly, if the SQL works, you don't need any other solution.

    Perhaps you mean, what is the most efficient solution? In general, it's more efficient to do more work on the database server than outside the server, but sometimes, it doesn't. This can be due to the wanted result, or it can be because of a slow database server, and a fast machine to do calculations on.

    Since your local conditions are impossible to know for us, there's just one way to solving this question: benchmark.

    Abigail

[reply]
    A reply falls below the community's threshold of quality. You may see it by logging in.
Re: SQL or Perl solution
by UnderMine (Friar) on Mar 08, 2004 at 15:47 UTC
    You should be able to do this in a single update 
    UPDATE Geo_postaggregated_pop_count_Ward
SET Sum_male_count =
        (SELECT SUM(G.Person_count)
           FROM Geo_postaggregated_pop_count_Ward G
          WHERE Sex = 1),
    Sum_female_count =
              (SELECT SUM(G.Person_count)
                 FROM Geo_postaggregated_pop_count_Ward G
                WHERE Sex = 2),
    Sum_of_person_count =
              (SELECT SUM(G.Person_count)
                 FROM Geo_postaggregated_pop_count_Ward G
                WHERE Sex = 1 OR
                      Sex = 2);

    [download]
    This assumes you can afford the large lock this would generate. Otherwise use a Transact SQL cursor.

    Hope it helps
    UnderMine

[reply]
[d/l]
    A reply falls below the community's threshold of quality. You may see it by logging in.
Re: SQL or Perl solution
by Plankton (Vicar) on Mar 08, 2004 at 16:29 UTC
    You might want to keep in mind that SQL is a 4th generation language (4GL) while Perl is a 3GL.

  • 1GL is machine code.
  • 2GL is assembly language
  • 3GL includes languages like (Perl, C++, Java, Cobol, PL/1, PL/SQL, Ada, etc.)
  • 4GL's are designed to be more declarative. Example languages would be SQL and Prolog.

    Control statements (if-else, for, while, switch) are characteristic of 3GL's. In 4GL the language decides the underlying program flow of control for you.

    Plankton: 1% Evil, 99% Hot Gas.
[reply]

Back to Seekers of Perl Wisdom