Several update statement query methods

Zheng Wen:

Database update is a method of update,
Standard Format: Update table name set field = value where Condition
The data sources are different:

1. Input from outside
This is simpler than the limit.
Example: Update TB set username = "XXXXX" where userid = "aasdd"

2. Some internal variables and functions, such as time
Directly assign a function to a field
Update TB set lastdate = Date () Where userid = "aasdd"

3. For some field variables + 1, common examples include click-through rate and download times.
In this way, the field + 1 is directly assigned to itself.
Update TB set clickcount = clickcount + 1 where id = xxx

4. assign a field of the same record to another field.
Update TB set lastdate = regdate Where xxx

5. Update a batch of records in one table to another.
Table1
Id F1 F2
Table 2
Id F1 F2
First, update F1 F2 in Table2 to Table1 (same ID)

Update Table1, Table2 set table1.f1 = table2.f1, table1.f2 = table2.f2 where table1.id = table2.id

6. update some records in the same table to other records.
Table:
Id month e_id price
1 1 1 2
2 1 2 4
3 2 1 5
4 2 2 5
First, we need to update the product price in July to July.
Obviously, we need to find the same e_id as the ID in March and march, and update the price to mid March.
This can be completely processed with the above method, just because the same table, in order to distinguish two months, you should rename the table
Update a, a as B set a. Price = B. Price Where a. e_id = B. e_id and A. month = 1 and B. month = 2

Of course, we can also query the results in February and update them in the 5. method.

Update A, (select * from a where month = 2) as B set a. Price = B. Price Where a. e_id = B. e_id and A. month = 1

 

Several update statement query methods