Example of updating multiple data entries in ms SQL Server and MySQL

1.
Use Dynamic table name in ms SQL SERVER: declare @ tablename nvarchar (160)
Set @ tablename = 't_ stat_all'
Declare @ SQL nvarchar (160)
Print @ tablename
Set @ SQL = 'select count (*) from '+ @ tablename
Exec (@ SQL)

2. MySQL Example 1
Statement Update (select SC, TOS, sum (click) as click, product, adpid from log_ SC _click group by SC, TOS, product, adpid) as,
T_stat_ SC _h_tmp as B
Set B. SC _click = A. Click
Where B. stat_date = str_date and B. Hour = str_hour and B. SC = A. SC
And B. TOS = A. ToS and B. Product = A. Product and A. adpid = B. adpid;

 
3. MySQL Example 2
Update t_advertiser as,
(Select uid, sum (amount) as amount from t_trade where status = '1' and type = '4' group by UID) as B
Set a. Spend = (B. amount), A. Balance = (A. totalAmount-b.amount)# Note that comma is not and
Where a. ID = B. uid;

 

4.
Complete SQL Server Stored Procedure example

Set ansi_nulls on
Set quoted_identifier on
Go

-- Alter procedure [DBO]. [ad_stat]
Alter procedure [DBO]. [ad_stat]
@ Day varchar (20) = NULL

As
Begin try

If (@ day is null)
Set @ day = convert (varchar (10), dateadd (day,-1, getdate (), 121)

Declare @ theday datetime
Set @ theday = cast (@ day as datetime)

Declare @ Yesterday varchar (10)
Set @ Yesterday = convert (varchar (10), @ theday, 121)

Declare @ tablename nvarchar (160)
Set @ tablename = 'Log _ adlist _ '+ Left (@ yesterday, 4) +' _ '+ substring (@ Yesterday, 6, 2) +' _ '+ substring (@ yesterday, 9, 2)

Declare @ SQL nvarchar (500)
Set @ SQL = 'Update t_stat_all '+
'Set Cl = B. Click from' +
'(' +
'Select alltype as ad_id, posid as posid, count (*) as click' +
'From' + @ tablename +
'Where datediff (D, visittime, '+ @ theday +') = 0' +
'Group by alltype, posid '+
') B, t_stat_all a' +
'Where datediff (D, A. stat_date, '+ @ theday +') = 0 and A. posid = B. posid '+
'And A. ad_id = B. ad_id ';
Exec (@ SQL)

End try
Begin catch
Insert into actionlogs
([Createtime]
, [Actionname]
, [Type]
, [Infor])
Values
(Getdate (),
'Ad _ stat ',
'Error', -- error, Info
Error_message ())
End catch