Oracle update set from statement format

Source: Internet
Author: User

To make the memo field value in Table B equal to the name value of the corresponding id in Table
Table a: id, name
1 Wang
2 Li
3
Table B: id, ClientName
1
2
3
(Ms SQL Server) Statement:
SQL code
Update B set ClientName = a. name from a, B where a. id = B. id
(Oralce) Statement:
SQL code
Update B set (ClientName) = (SELECT name FROM a WHERE B. id = a. id)
 
 
When both where and set need to be associated with a table for query, during the entire update execution, the associated table needs to be scanned twice, which is obviously less efficient.
In this case, the solution for Sybase and SQL SERVER is to use the UPDATE... SET... FROM... WHERE... syntax, which is actually to get the updated data FROM the source table.
In SQL, table join (left join, right join, inner join, etc.) is often used in select statements. In SQL syntax, these connections can also be used in update and delete statements, in these statements, using join results often get twice the result with half the effort.
 
SQL code
Update T_OrderForm SET T_OrderForm.SellerID = B. L _ TUserID
FROM T_OrderForm a left join T_ProductInfo B on B. L _ ID = A. ProductID
 
 
Used to synchronize data from two tables!
Both Oralce and DB2 support the following syntax:
 
 
SQL code
Update a set (A1, A2, A3) = (SELECT B1, B2, B3 from B where a. ID = B. ID)
Ms SQL Server does not support such a syntax, the corresponding syntax is:
 
 
SQL code
Update a set A1 = B1, A2 = B2, A3 = B3 from a left join B on a. ID = B. ID
 
I personally feel that the Update Syntax of ms SQL Server is more powerful. Ms SQL SERVER writing:
 
SQL code
Update a set A1 = B1, A2 = B2, A3 = B3 from a, B where a. ID = B. ID
Writing in Oracle and DB2 is troublesome, as follows:
 
SQL code
Update a set (A1, A2, A3) = (SELECT B1, B2, B3 from B where a. ID = B. ID)
Where id in (select B. id from B where a. ID = B. ID)

Author "w8700569"

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.