mysql 1242_#SQL1242错误

子查询更新数据时遇到多条数据时，可以使用SUM&MIN等函数解决：如下：

//正确的方法一对多UPDATE `yd_draw_prize_order` SET `adopt_update_time` = (SELECT MIN(`update_time`) FROM `yd_draw_prize_order_number` WHERE `d_p_order_id` = `yd_draw_prize_order`.`d_p_order_id`) WHERE `adopt_update_time` IS NULL

//报1242错误方式一对多UPDATE `yd_draw_prize_order` SET `adopt_update_time` = (SELECT `update_time` FROM `yd_draw_prize_order_number` WHERE `d_p_order_id` = `yd_draw_prize_order`.`d_p_order_id`) WHERE `adopt_update_time` IS NULL

View Code

时间查询     简单举例： SELECT COUNT('$column') FROM `%table` WHERE `$column` BETWEEN UNIX_TIMESTAMP('2018-05-03 00:00:01') AND UNIX_TIMESTAMP('2018-05-03 23:59:59')

更多时间查询案例；

案例分享：

//FROM_UNIXTIME 将时间戳转换为字符串日期;

//UNIX_TIMESTAMP 将其他时间转换为时间戳;

SELECT

`yd_draw_prize_order`.`d_p_order_id`,

yd_draw_prize_order_log.`rand_num`,

FROM_UNIXTIME(yd_draw_prize_order.adopt_time) AS adopt_time,

FROM_UNIXTIME(

yd_draw_prize_order_log.draw_time

) AS '抽奖时间',

yd_draw_prize_order_log.`d_p_order_activity_id`,

yd_draw_prize_order_log.`d_p_order_activity_name`,

yd_draw_prize_order_log.`draw_num`,

yd_draw_prize_order_log.`winning`

FROM

`yd_draw_prize_order`

INNER JOIN `yd_draw_prize_order_number`

ON yd_draw_prize_order.d_p_order_id = yd_draw_prize_order_number.d_p_order_id

INNER JOIN `yd_draw_prize_order_log`

ON yd_draw_prize_order.d_p_order_id = yd_draw_prize_order_log.d_p_order_id

WHERE `customer_phone` = '15512816085'

AND `adopt` = 1

AND yd_draw_prize_order_number.`reward_num` = 04028

AND yd_draw_prize_order_log.draw_time > UNIX_TIMESTAMP(NOW())

AND yd_draw_prize_order_log.`d_p_order_activity_id` = 2

案例2

//日期子查询;

SELECT

*

FROM

`yd_extension_data_1`

WHERE `distrbutor_id` IN

(SELECT

`distrbutor_id`

FROM

`yd_distrbutor`

WHERE `distrbutor_provinceid` = 19)

AND `create_time` BETWEEN '2017-01-01 00:00:01'

AND '2018-01-01 00:00:00'

AND `distrbutor_id` NOT IN (1,23)

ORDER BY `create_time`

综合查询&&综合更新

综合查询案例1：

1.SELECTb.idAS '用户id',user_name AS '用户名',

`active_points`AS '积分',COUNT(bet_state) AS '猜中数',

GROUP_CONCAT(bet_state)AS '统计情况',

GROUP_CONCAT(match_id)AS '赛事ID',

b.shareAS '1为己分享',

`use_points`AS '消耗积分'

FROMyd_draw_worldcup_bettingASaINNER JOIN yd_draw_worldcup_user ASbWHERE a.`worldcup_user_id` =b.`id`AND a.`bet_state` IN (20, 21, 22)AND b.`use_points` <> 0

GROUP BYid;2.SELECTb.idAS '用户id',user_name AS '用户名',

`active_points`AS '积分',

b.share+ COUNT(bet_state) AS '自定义',COUNT(bet_state) AS '猜中数',

b.shareAS '1为己分享',

`use_points`AS '消耗积分'

FROMyd_draw_worldcup_bettingASaINNER JOIN yd_draw_worldcup_user ASbWHERE a.`worldcup_user_id` =b.`id`AND a.`bet_state` IN (20, 21, 22)AND b.`use_points` = 0

GROUP BY id;

View Code

综合简单查询更新1：

1.UPDATE`yd_draw_worldcup_user`SET`active_points`= `active_points` + 1

WHERE `id` IN(SELECT`worldcup_user_id`FROM`yd_draw_worldcup_betting`WHERE `match_id` = 2

AND `bet_state` IN (20, 21, 22))

View Code

综合链表更新2：

1.update yd_draw_worldcup_user as a,yd_draw_worldcup_betting as b seta.active_points= 0,

b.bet_state= 10,

b.draw_number= ''

where b.match_id in (1,2,3,4,5)and b.worldcup_user_id = a.id;

View Code

判断语句：

UPDATE yd_draw_worldcup_user AS a

SET a.active_points =

(CASE WHEN (a.active_points + a.`share` < a.use_points) THEN 0 ELSE (a.active_points + a.`share` - a.use_points) END)

WHERE 1;

事务语句：

#mysql使用事务的关键字

#begin //打开一个事务

#commit //提交到数据库

#rollback //取消操作

#savepoint //保存，部分取消，部分提交

#alter table person type=INNODB //修改数据引擎

begin;

update tags set tagid = 133 where docid = 1;

SAVEPOINT tags1;

update tags set tagid = 530 where docid =2;

SAVEPOINT tags2;

ROLLBACK TO SAVEPOINT tags2;

SELECT * from tags where docid in(1,2);

commit;

自定义序列号字段:

SET @rownum=0;

SELECT

@rownum:=@rownum+1 AS '序列',

a. AS '用户名称'

FROM

(SELECT @rownum:=0) r, AS a

WHERE 1

ORDER BY . ASC

mysql update You can't specify target table 'yd_qr_code' for update in FROM clause .

原：update `yd_qr_code` set winning_description =  (select a.`winning_description` from `yd_qr_code` as a where a.`qr_id` = 1) where qr_id = 2

原理：mysql 不能在同表操作更新，我们要用一个 中间表 来让数据库认为不是同表操作；

后：update `yd_qr_code` set winning_description = (select b.`winning_description` from (select a.`winning_description` from `yd_qr_code` as a where a.`qr_id` = 1)b) where qr_id = 2

【查询】数据库有哪些表；

select table_schema asdatabase_name,

table_namefrominformation_schema.tableswhere table_type = 'BASE TABLE'

and table_name like '%__value%'

order bytable_schema,

table_name;

View Code