SQL updates multiple fields with subquery results. SQL queries table fields.

SQL updates multiple fields with subquery results. SQL queries table fields.

Author: iamlasong

Requirement: The table content needs to be changed. The changed content should be placed in a temporary table, and multiple fields in the formal table should be updated using SQL statements.

If you update a field, you can directly use the field name = subquery. Update multiple fields and write them out in parentheses as follows:

Update tb_jg t
Set t. jgfl = 'sd ',
(T. zj_code, t. zj_mc) = (select a. zj_code, a. zj_mc
From song_temp
Where a. zj_code = t. zj_code)
Where exists (select 1 from song_temp a where a. zj_code = t. zj_code)

Requirement: Create a table based on the subquery. The statement is as follows:

Create table sncn_zd_jg as select * from tb_jg where zdbz = '1' order by city_code, xs_code;

If the table already exists, the following statement inserts the query result:
Insert into sncn_zd_jg select * from tb_jg where zdbz = '1' order by city_code, xs_code;

Multiple values of a field in SQL query are common:
Select * from t1
Where exists (select * from (select name, number, max (money) as money from t1 group by name, number) as tmp
Where name = t1.name and number = t1.number
And money = t1.money)
In SQL server, when multiple tuples with subqueries are updated at the same time, the preceding error occurs: The returned value indicates that the subquery end returns multiple fields and only accepts the fields (that is, the value =: indicates a single field) = changed to INOK
Update dd
Set dprice IN (select sum (book. price * orderdetail. number) from orderdetail, book, dd
Where book. bookno = orderdetail. bookno and dd. ddno = orderdetail. ddno
Group by dd. ddno)
From orderdetail, book
Where orderdetail. bookno = book. bookno
------------------------------------