语法1:
update
table1 INNER JOIN table2
ON condition_expr
SET
col1 = {expr1|DEFAULT}
col1 = {expr2|DEFAULT}
WHERE where_conditions
语法2:
update
table1 LEFT JOIN table2
ON condition_expr
SET
col1 = {expr1|DEFAULT}
col1 = {expr2|DEFAULT}
WHERE where_conditions
语法3:
update
table1 RIGHT JOIN table2
ON condition_expr
SET
col1 = {expr1|DEFAULT}
col1 = {expr2|DEFAULT}
WHERE where_conditions
案例:
需求:
今天身份证号为210210199901012222的读者将超限的图书20151101归还,根据描述实现如下需求:
1.更新借阅信息表,将借阅状态(status)更新为‘是’。
2.更新罚款记录信息表,更新实际还书日期和罚款金额,罚款金额为每超出一天扣0.2元。
3.同时更新读者信息表的余额。(在余额中扣除罚款金额)
SQL语句:
select * from borrowinfo;
select * from readerfee;
update readerfee t1
join readerinfo t2
on t1.card_id = t2.card_id
set
actual_return_date = sysdate(),
book_fee=datediff(sysdate(),return_date)*0.2,
balance = balance - book_fee
where t1.book_id = 20151101 and t1.card_id = '210210199901012222';
select * from readerinfo;
转载于:https://www.cnblogs.com/apollo1616/articles/9821395.html