多条语句更新执行如:
UPDATE `itop233`.`priv_change` SET `date`='2015-07-31 08:08:32' WHERE `id`='33';
UPDATE `itop233`.`priv_change` SET `date`='2015-07-31 08:09:18' WHERE `id`='34';
UPDATE `itop233`.`priv_change` SET `date`='2015-07-31 08:10:43' WHERE `id`='35';
UPDATE `itop233`.`priv_change` SET `date`='2015-07-31 08:12:11' WHERE `id`='37';
UPDATE `itop233`.`priv_change` SET `date`='2015-10-11 09:28:00' WHERE `id`='69';
UPDATE `itop233`.`priv_change` SET `date`='2015-10-11 09:38:47' WHERE `id`='72';
UPDATE `itop233`.`priv_change` SET `date`='2015-10-12 08:07:45' WHERE `id`='82';
UPDATE `itop233`.`priv_change` SET `date`='2015-10-12 08:08:48' WHERE `id`='83';
MySql批量更新:
UPDATE yoiurtable
SET dingdan = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END
WHERE id IN (1,2,3)
这句sql 的意思是,更新dingdan 字段,如果id=1 则dingdan 的值为3,如果id=2 则dingdan 的值为4……
where部分不影响代码的执行,但是会提高sql执行的效率。确保sql语句仅执行需要修改的行数,这里只有3条数据进行更新,而where子句确保只有3行数据执行。
例子:UPDATE book
SET Author = CASE id
WHEN 1 THEN '黄飞鸿'
WHEN 2 THEN '方世玉'
WHEN 3 THEN '洪熙官'
END
WHERE id IN (1,2,3)
如果更新多个值的话,只需要稍加修改:
UPDATE categories
SET dingdan = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END,
title = CASE id
WHEN 1 THEN 'New Title 1'
WHEN 2 THEN 'New Title 2'
WHEN 3 THEN 'New Title 3'
END
WHERE id IN (1,2,3)
例子:UPDATE book
SET Author = CASE id
WHEN 1 THEN '黄飞鸿2'
WHEN 2 THEN '方世玉2'
WHEN 3 THEN '洪熙官2'
END,
Code = CASE id
WHEN 1 THEN 'HFH2'
WHEN 2 THEN 'FSY2'
WHEN 3 THEN 'HXG2'
END
WHERE id IN (1,2,3)**
到这里,已经完成一条mysql语句更新多条记录了。
好了,很有用,记录下来,具体的Book的表结构不写了,大家可以建立自己的表结构,字段改成自己的字段就可以了。MySql是挺好用的。
#='001e92d8-06cb-4035-8554-ebe548b3172b'
SELECT
*
FROM
info_orders io
INNER JOIN
(SELECT DISTINCT
id
FROM
(SELECT
io.id
FROM
info_orders io,
info_order_details iod
INNER JOIN
(SELECT
ic.id
FROM
info_case ic,
user_store us
WHERE ic.`create_man` = us.`user_id`
AND us.`user_id` =@user_id) e
ON iod.`product_id` = e.id
WHERE io.`order_no` = iod.`order_no`
AND io.`order_product_type` = 1
UNION
ALL
SELECT
io.id
FROM
info_orders io,
info_order_details iod
INNER JOIN
(SELECT
ic.id
FROM
info_case ic,
user_store us
WHERE ic.`create_man` = us.`user_id`
AND us.`user_id` = @user_id) e
ON iod.`product_id` = e.id
WHERE io.`order_no` = iod.`order_no`
AND io.`order_product_type` = 2
UNION
ALL
SELECT
io.id
FROM
info_orders io,
info_order_details iod
INNER JOIN
(SELECT
ic.id
FROM
info_case ic,
user_store us
WHERE ic.`create_man` = us.`user_id`
AND us.`user_id` = @user_id) e
ON iod.`product_id` = e.id
WHERE io.`order_no` = iod.`order_no`
AND io.`order_product_type` = 3) e) e
ON e.id = io.`id`
LEFT JOIN info_order_details iod
ON io.`order_no` = iod.`order_no`
DCLARE @user_id VARCHAR;
set @user_id = '322d01ef-8a7a-4984-94f0-c383b014c1ec';
select * from info_order_details iod
WHERE iod.product_id IN(
SELECT ic.id FROM info_case ic,user_store us WHERE ic.`create_man` = us.`user_id`
AND us.`user_id` =@user_id
UNION
SELECT tgb.id FROM tax_goods_books tgb,user_store us WHERE tgb.`user_id` = us.`user_id`
AND us.`user_id` = @user_id
UNION
SELECT tgb.id FROM tax_goods_base tgb, user_store us WHERE tgb.`user_id` = us.`user_id`
AND us.`user_id` = @user_id
) ORDER BY iod.create_time desc;
查询订单记录
SELECT
a.order_no AS '订单号',
b.product_id AS '商品id',
(
CASE a.product_type
WHEN 1 THEN
e.`name`
WHEN 3 THEN
d.title
END
) AS '商品名',
b.quantity AS '数量',
a.money AS '订单金额',
(
CASE a.order_type
WHEN 1 THEN
'在线支付订单'
WHEN 2 THEN
'电话订购订单'
END
) '订单类型',
(
CASE a.product_type
WHEN 1 THEN
'工作指引'
WHEN 2 THEN
'培训视频'
WHEN 3 THEN
'案例'
END
) '商品类型',
(
CASE a. STATUS
WHEN 0 THEN
'待支付'
WHEN 1 THEN
'已支付'
WHEN 2 THEN
'已完成'
WHEN 3 THEN
'已取消'
WHEN 4 THEN
'已删除'
END
) '订单状态',
(
CASE a.payment_type
WHEN 1 THEN
'微信支付'
WHEN 2 THEN
'支付宝END'
ELSE
''
END
) AS '支付方式',
a.user_id AS '用户id',
c.user_name AS '用户名',
c.mobile AS '手机号',
(
CASE c.gender
WHEN 1 THEN
'男'
WHEN - 1 THEN
'女'
ELSE
''
END
) AS '性别',
a.payment_time AS '支付时间',
a.created_date AS '创建时间',
a.updated_date AS '更新时间'
FROM
taxtao_user_order a
LEFT JOIN taxtao_user_order_detail b ON a.id = b.order_id
LEFT JOIN taxtao_user_account c ON a.user_id = c.id
LEFT JOIN taxdb.info_case d ON d.id = b.product_id
AND a.product_type = 3
LEFT JOIN taxtao_book_base_info e ON e.id = b.product_id
AND a.product_type = 1
WHERE
a.order_type = 1
ORDER BY
a.updated_date DESC;
把表fl_tax_law_lib_1的数据插进表flysoft:
INSERT INTO flysoft (
oldID,
SF_TYPE,
NAME,
WENHAO,
CONTENT,
ISSUE_DATE,
INVALIDDATE,
WHERE_ISSUE,
TAX_TYPE,
BUSINESS_TYPE,
MODIFYTIME,
URL,
id,
is_no,
update_time,
) SELECT
fl_no,
sf_type,
title,
wenhao,
content,
issue_date,
invalid_date,
where_issue,
tax_type,
business_type,
modify_time,
linkurl,
mark,
0,
now()
FROM
fl_tax_law_lib_1
更新多个字段 update select inner join
在遇到需要update设置的参数来自从其他表select出的结果时,需要把update和select结合使用,不同数据库支持的形式不一样,在mysql中如下:
update A inner join(select id,name from B) c on A.id = c.id set A.name = c.name;
根据AB两个表的id相同为条件,把A表的name修改为B的sql语句就如上所示