Update Statements

In the tutorial on where statements we found out there are errors in our database. The award_offered amount cannot logically be less than the award_accepted amount. Also the award accepted amount cannot be less than the award_disbursed amount. Yet some accepted amounts are more than the offered amount.

SELECT a.award_id, a.award_fund, a.award_offered, a.award_accepted, a.award_disbursed FROM award AS a
WHERE a.award_offered < a.award_accepted
OR a.award_accepted < a.award_disbursed;

award_id	award_fund	award_offered	award_accepted
X00001	SUB	102.0	202.0
X00001	UNSUB	103.0	2002.0
X00002	SUB	202.0	2202.0
X00002	UNSUB	203.0	2333.0
X00004	SUB	402.0	4202.0

Updates

We are going to fix the X00004 SUB loan record first. The accepted amount if 4202 but it was supposed to be 402. To update the record we need to isolate the record. We need to write a SELECT statement that selects only that record.

SELECT award_id, award_fund, award_offered, award_accepted, award_disbursed FROM award AS p
WHERE award_id = 'X00004'
AND award_fund = 'SUB';

To update the data use the UPDATE keyword followed by the name of the table that will be updated. Use the SET keyword, the column to be updated, and the new value. Finally, include your complete WHERE statement to select the exact record (or records) to replace. As always, remember to end the code with a semicolon.

UPDATE award
SET award_accepted = 402
WHERE award_id = 'X00004'
AND award_fund = 'SUB';

Updating Multiple Records

We now have four records that still need updating. Now we need to fix the remaining erroneous subsidized loan records. As a best practise we will first select those records to make sure our where clause is working correctly.

SELECT * FROM award as a
WHERE a.award_fund = 'SUB'
AND a.award_accepted > a.award_offered;

Then we use the WHERE clause to update the records.

UPDATE award
SET award_accepted = 0
WHERE award_fund = 'SUB'
AND award_accepted > award_offered;

For the UNSUB loans we are going to set the accepted amount equal to the offered amount.

SELECT * FROM award as a
WHERE a.award_fund = 'UNSUB'
AND a.award_accepted > a.award_offered;

UPDATE award
SET award_accepted = award_offered
WHERE award_fund = 'UNSUB'
AND award_accepted > award_offered;

But what if we also need to make sure the disbursed amount is zero. We can append the column/value combination with a comma. We can adjust as many columns as we want as long as we separate them with commas.

UPDATE award
SET award_accepted = award_offered, award_disbursed = 0
WHERE award_fund = 'UNSUB'
AND award_accepted > award_offered;