1,不知道客户的id,只知道姓名,比如我知道客户的名字是myworks,我要更改他的所有发票内容如下
update invoices
set
payment_total=invoice_total*0.5,
payment_date=due_date
where client_id=
(select client_id
from clients
where name='Myworks')
多选+tab可以缩进
1 row(s) affected, 1 warning(s): 1265 Data truncated for column 'payment_total' at row 2 Rows matched: 1 Changed: 1 Warnings: 1
2,更改在CA和NY的顾客发票信息
update invoices
set
payment_total=invoice_total*0.5,
payment_date=due_date
where client_id=
(select client_id
from clients
where state in ('CA','NY'))
此处出错Error Code: 1242. Subquery returns more than 1 row
因为选中子查询发现有多行
因此需要改为
update invoices
set
payment_total=invoice_total*0.5,
payment_date=due_date
where client_id in
(select client_id
from clients
where state in ('CA','NY'))
3,假设没有支付日期的,我们要按照设定换掉
update invoices
set
payment_total=invoice_total*0.5,
payment_date=due_date
where payment_date is null
4,练习:
store数据库中,orders中超过3000积分的顾客,如果下过订单,就更新comments为gold customers
答案:
update orders
set comments='gold customer'
where customer_id in #第二步 更改orders中的评论内容,范围在第一步的顾客ID内
(select customer_id
from customers
where points>3000) #第一步 选中积分大于3000的顾客id