topic_1.2_insert_update_delete

# insert a row:

insert into customers
values (
  default, 
  'John', 
  'Smith', 
  '1991-01-01',
  NULL,
  'address',
  'city',
  'CA',
  default)


insert into customers (
    first_name,
    last_name,
    birth_date,
    adress,
    city,
    state)
values (
  'John', 
  'Smith', 
  '1991-01-01',
  'address',
  'city',
  'CA')

# insert muliple rows:
insert into shippers (name)
values 
  ('Shipper1'),
  ('Shipper2'),
  ('Shipper3')


# insert hierarchical data into multiple tables:
# the table order (order_id, customer_id, order_date, status, ...) and order_items (order_id, product_id, quantity, unite_price) have a parent child relationship.
# an order may have multiple order_items.

insert into orders (customer_id, order_date, status)
values (1, '2019-01-02', 1)

insert into order_items
values 
  (last_insert_id(), 1, 1, 2.5),
  (last_insert_id(), 2, 1, 3.5)

# create a copy of table:
create table orders_archived as
select * from orders

# attributes of the above new table is ignored.
# truncate the above table and insert using subquery:
insert into orders_archived
select *
from orders
where order_date < '2019-01-01'

# update data in a single row:
update invoices
set payment_total = 10, payment_date = '2019-01-01'
where invoice_id = 1

# set one column equal to another column in the table:
update invoices
set payment_total = 10, payment_date = due_date
where invoice_id = 1

# udpate multiple rows in a table:
# set safe update in mysql workbench.
update invoices
set payment_total = invoice_total * 0.5, payment_date = due_date
where client_id in (3, 4)

# using subqueries in updates:
update invoices
set
  payment_total = invoice_total * 0.5
  payment_date = due_date
where client_id in (
  select client_id
  from clients
  where name = 'Myworks'
)

# delete rows:
delete from invoices
where invoice_id = (
select *
from clients
where name = 'Myworks'
)