Difference between revisions of "SQL UPDATE"

// Update all accounts that are now overdue by adding a 15% commission charge
 
UPDATE accounts;
  SET ord_value=ord_value*1.15, due_date = date()+30;
  WHERE paid_date < date()
 
// Declare the cursor to select records from the accounts table
DECLARE accounts;
  CURSOR FOR;
  SELECT name, address, ord_value, balance;
  FROM accounts;
  WHERE ord_date < date() 
 
// Open the cursor
OPEN accounts
 
// Fetch records one at a time from the cursor and update them
FETCH accounts;
  INTO m_name, m_address, m_ord_value, m_balance
do while sqlcode = 0
    if not empty(m_name) and m_balance <> 0
        UPDATE accounts;
          SET ord_value = ord_value*1.15, due_date = date()+30;
          WHERE CURRENT OF accounts
    endif
    FETCH accounts INTO m_name, m_address, m_ord_value, m_balance
enddo
 
// Close the cursor and free up any resources used for the cursor
CLOSE accounts
DROP CURSOR accounts
 
// Update an encrypted table
UPDATE encacc<key_1,key_2,key_3>;
  SET ord_value=ord_value*1.15, due_date = date()+30;
  WHERE paid_date < date()