sql procedures

[eon]

Is there a way in sql (im using psql) to loop through an entire table and make an insert call on each entry based on some other attribute

example:

table worker
{
-type: manager or employee
-id
}
table payment_log
{
-worker.id
-hours
-amount earned
}
-Loop through each worker in the table, based on their worker type insert into the payment log for tthe amount they earned that day

thanks

 

[Earp]

If you really want to loop over a result set in SQL, you would use a cursor. I'm only familiar with TSQL (Microsoft SQL Server), but any decent SQL server should have something similar.

That said, if you find yourselft using a cursor, you're usually doing something wrong. You can usually do it using queries.

You're thinking in terms of procedural languages, but in SQL things work differently. Typically you'd do something like this:

INSERT INTO DestinationTable SELECT ID, computedval FROM SourceTable

 

[mikeblas]

It's possible to write a single INSERT statement to do what you want. Your problem description isn't complete enough for us to do it for you, but so far you don't need a cursor or any procedural code.

 

[Oneos]

I'm going to make a few assumptions here.

You are using some sort of program to insert into the payment log, providing the worker id and the hours worked. I am assuming this because so far there is no value for hours stored.

At the end of each week, you want this query to run to update the payment log, using the hour value to determine amount earned.

Managers earn 1 wage.
Employees earn 2 wage.

In order to do this, I'm going to use PHP and MySQL.

//$id and $hours are from submitted form

$type = mysql_query("SELECT type FROM `worker` WHERE id = '$id'");

if ($type == "manager")
    {
    $wage = 12;
    }
else
    {
    $wage = 10;
    }

$earned = $wage * $hours;

mysql_query("INSERT INTO `payment_log` VALUES ('$id', '$hours', '$earned')");

Something like that may work, assuming it is in fact what you want to do. Don't quote me on it though.

 

[Arainach]

Another possibility: Have a 'wage' table with 'type' and 'salary' columns. (Personally, I'd think it would be best to have the salary column in the worker table so you could have per-employee settings, but that's up to you). In that case it could be done as

INSERT INTO `payment_log` SELECT '$id', '$hours', wage.salary * $hours FROM worker LEFT JOIN wage ON wage.type = worker.type WHERE worker.id='$id'

(assuming PHP as the above post did)