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()
 
// Example using a cursor
OPEN DATABASE southwind
 
// Declare the cursor to select records from the orders table
DECLARE cursor1;
  CURSOR FOR;
  SELECT orderid, customerid, orderdate;
  FROM orders;
  WHERE requireddate < date()
 
// Open the cursor
OPEN cursor1
 
// Fetch records one at a time from the cursor and update them
FETCH cursor1;
  INTO m_order, m_customer, m_orderdate
  do while sqlcode = 0
    if not empty(m_order) and empty(shippeddate)
        UPDATE orders;
          SET shippeddate = date();
          WHERE CURRENT OF cursor1
    endif
    FETCH cursor1;
      INTO m_order, m_customer, m_orderdate
enddo
 
// Close the cursor and free up any resources used for the cursor
CLOSE cursor1
DROP CURSOR cursor1
 
CLOSE DATABASES
 
// Example with 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()