INSERT/UPDATE Help

[focus310]

Hello:

I'm trying to write a query which will update values after they were inserted.

Once a set of rates is inputed for a new lender and program, the rates will be inserted into the table. Later, if there is a new set of rates for the program/lender which was just inserted, I want to update those rates.

How do I go about writing my update statement?

Thank you for the help.

This is my insert statement:
$query2 = "INSERT INTO rates (program_id, program_code, lender_id, lender_code, rate, price, date, time)
VALUES ('$_SESSION[program_id]','$_SESSION[program_code]','$_SESSION[lender_id]','$_SESSION[lender_code]','$_SESSION[rate1]','$_SESSION[price1]', CURRENT_DATE, CURRENT_TIME),
('$_SESSION[program_id]','$_SESSION[program_code]','$_SESSION[lender_id]','$_SESSION[lender_code]','$_SESSION[rate2]','$_SESSION[price2]', CURRENT_DATE, CURRENT_TIME),
('$_SESSION[program_id]','$_SESSION[program_code]','$_SESSION[lender_id]','$_SESSION[lender_code]','$_SESSION[rate3]','$_SESSION[price3]', CURRENT_DATE, CURRENT_TIME),
('$_SESSION[program_id]','$_SESSION[program_code]','$_SESSION[lender_id]','$_SESSION[lender_code]','$_SESSION[rate4]','$_SESSION[price4]', CURRENT_DATE, CURRENT_TIME)";

[Taschen]

UPDATE table_name
SET col_name1=value1
WHERE where_condition

The full syntax can be found here
http://dev.mysql.com/doc/refman/5.0/en/update.html

[focus310]

Hi,

How do I update multiple rows? There are four rows for each lender/program combination.

Here's an example. These are the rates currently set for program_id = 1 and lender_id = 1.

program_id | lender_id | rate
----------------------------
1 | 1 | 5.500
1 | 1 | 5.750
1 | 1 | 6.000
1 | 1 | 6.125

Now, the rates for this program/lender has changed to the following:


program_id | lender_id | rate
----------------------------
1 | 1 | 6.000
1 | 1 | 6.750
1 | 1 | 7.000
1 | 1 | 7.125

When the person enters the new rates on the form, I want to update the table with those new rates for the program/lender which was selected.

How do I write the syntax to update all four of these rates for program_id = 1 and lender_id = 1?

I searched for the update syntax on many websites and they don't seem to talk about this particular situation.

[buntine]

Rather than using an UPDATE statement, I would actually delete the current rates and then insert new ones

For example, provided we know both the program_id and lender_id values, we can delete only the appropriate records:

DELETE FROM your_table WHERE program_id=<PROGRAM_ID> AND lender_id=<LENDER_ID>;

Then we can immediately insert the new values:

INSERT INTO your_table (program_id, lender_id, rate) VALUES (<PROGRAM_ID>, <LENDER_ID>, <NEW_RATE_VALUE>);

This INSERT query will need to be run for each new rate, of coarse.

This solution should work fine, provided the primary key of this particular table is not required to keep referential integrity!

Cheers,
Andrew Buntine