insert into select/update join

#select结果多条的时候会插入多条,FIND_IN_SET
INSERT INTO `test`.`sign` (  `invoice`, `number`, `sign_time` )
select id,'21023541',NOW() 
from crm_invoice  a 
where  FIND_IN_SET('21023541',invoice_numbers)

update military_order a,business_company b 
set a.company_id=b.id 
where a.order='20220526170000001'

#update join 关联更新

UPDATE invoice a
JOIN invoice_info b ON a.id = b.invoice_id
JOIN order c ON c.der = a.sn 
SET a.rate = 0.97,
a.discount_sum = a.sum * 0.97,
a.discount_sum_count = a.sum_count * 0.97,
b.discount_price = b.unit_price * 0.97,
b.discount_total = b.subtotal * 0.97,
b.ate = 97.00,
c.ate = 97.00 
WHERE
	a.ordersn IN ( '2030833002' )

#插入关联订单对应的订单商品,商品随机从商品表中获取
INSERT INTO `test`.`order_goods`(`order_id`, `goodsid`, `goods_name`, `product_code`, `num`, `price`, `subtotal`, `create_time`, `update_time`) 
select id,tmp.goods_id,tmp.goods_name,tmp.productcode,5, 12.00, 60.00, NOW(), NOW() 
from dw_hs_order a,(select goods_id,goods_name,productcode from goods order by rand() limit 1)tmp  
where a.order_sn ='2022061008';

#需要反复执行直到Affected rows: 0,要不不会全部更新.set不是按照顺序执行的
#(联表关联更新set不支持顺序执行所以只执行一次会有数据错误)
UPDATE order a
JOIN order_goods b ON a.order_id = b.order_id
join invoice d on b.rec_id=d.rec_id
SET a.order_amount =12440.25,
b.discount_price =a.order_amount,
b.goods_price=ROUND((b.discount_price/b.goods_number),2),
b.rate = ROUND((b.goods_price/b.goods_price)*100,2),
d.discount_price=b.discount_price,
d.goods_price=b.goods_price,
d.rate=b.rate
WHERE
	a.order IN ( 
'250644710691'
	)

#只有一条数据的时候才会update修改			
UPDATE invoice a
join invoice_info b ON a.id = b.invoice_id
JOIN( SELECT *, COUNT(*) AS cnt FROM invoice_info GROUP BY invoice_id HAVING cnt = 1 )tmp ON tmp.id = b.id
JOIN order_goods n ON b.rec_id = n.rec_id 
JOIN goods d ON n.goods_id = d.goods_id,tax_categories c
SET b.class = c.class_name, 
b.class_code = c.class_code,
b.tax_rate = c.tax_rate,
d.class_name = c.class_name,
d.class_code = c.class_code
 -- ,b.NAME =  '高温脂'
 -- ,b.units = '箱'
 -- ,d.invoice_name = '高温脂' 
 -- ,d.unit = '箱'
WHERE
	a.ordersn = '271383879611' 
	AND c.class_name = '通用设备' 
	AND c.tax_rate = 13

#You can't specify target table 'm' for update in FROM clause报错所以用了嵌套再嵌套
#如果只有一条数据或者满足	product_code，goods_name的更新
UPDATE order_goods m,tax_categories c
SET m.tax_class = c.class_name, m.tax_class_code = c.class_code
WHERE c.class_name = '计算机配套产品'
AND c.tax_rate = 13
and m.id=( select id from (SELECT b.id
  FROM order a
  JOIN order_goods b ON a.id = b.order_id
  WHERE a.order_sn = 'HF20231114164923723640'
  AND (
	b.product_code = 'DWHF64598701' 
	OR b.goods_name = '' 
	OR ( SELECT COUNT( * ) FROM order_goods WHERE order_id = b.order_id ) = 1 
	))tmp)

注意事项:

1.join 关联的时候为空的数据应该用"字段 is NULL"