Batch update statement in sqlserver

Time:2020-9-27

Now I have two tables, S_ PERSON,S_ USER

S_PERSON 

S_USER

I want to put the s_ Modify account in user table to s in batch_ Account of person

We can find s_ There is an s in the user table_ The field associated with the person table is person_ ID, which is also the condition for us to update

After finding this relationship, it is not difficult for us to write SQL

update S_USER set account=p.account from S_PERSON p where p.id=S_USER.person_id;

The results were as follows

SQL server as syntax example

1. Use table name alias

There are two tables: people and product_ Orders”。 Give them aliases “P” and “Po” respectively. List all orders for “John Adams”.


SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p, Product_Orders AS po
WHERE p.LastName='Adams' AND p.FirstName='John';

2. Use column name alias

Query the LastName column (which defines the alias’ last name ‘) and the firstname column (defines the alias’ first name’) in the persons table and outputs all the result values.

Select LastName as last name, firstname as first name
FROM Persons

3. Use both table and column names

Define the alias’ a ‘for the city table, and use the table alias to query all information about the ID column in the table (define alias B for the ID column).


SELECT A.ID AS B
FROM city AS A; 

This article introduces here, I hope it can help you.