ORACLE basic SQL statements-add and update data functions, oraclesql
1. Add data
/* Add data */Insert into STU values ('stu0004', 'zhao yi', 18, 1, "kc0004 ");Insert into STU (STU_ID, STU_NAME, STU_AGE, STU_SET) values ('stu0013', 'Storage 11', 19,1 );
Note: If you do not specify the field to which the data is added, all data must be listed. If fields are listed, all fields whose constraints are not empty m
Oracle knowledge point Summary 1 (select from for update) 1. select from for update: When we use ORACLE for data processing, we sometimes need to lock the queried records and prohibit other processes from modifying the records. Oracle Database provides a way to use the selec
How to update a table using query results in Oracle
Oracle uses the results of one query to update the data of another table.
The simulation experiment is as follows. Use the query results of table t2 to update data with the same id as table t1.Drop table t1;Drop table t2;
C
In WL_HOME/wl_server10.3/server/lib, find classes12.zip (jdbc driver of earlier versions) ojdbc. jar or ojdbc6.jar.
View the META-INF/MANIFEST. MF in the package with the decompression tool
$ More MANIFEST. MF
Manifest-Version: 1.0
Implementation-Vendor: Oracle Corporation
Implementation-Title: ojdbc6.jar
Implementation-Version: Oracle JDBC Driver version-"11.1.0.7.0-Production"
Implementation-Time:
Oracle select for updateIn most cases, the rows checked by the cursor are modified for the processing completed in the extraction loop. PL/SQL provides a syntax for such processing.
This syntax includes two parts: the for update clause in the cursor declaration part and the where current of clause in the update or delete statement.
Generally, the Select opera
Environment:
mysql> show variables like '%storage_engine%';+----------------+--------+| Variable_name | Value |+----------------+--------+| storage_engine | InnoDB |+----------------+--------+1 row in set (0.00 sec)mysql> select version();+-----------+| version() |+-----------+| 5.1.52 |+-----------+1 row in set (0.06 sec)
SQL> select * from v$version where rownum=1;BANNER----------------------------------------------------------------Oracle Da
Oracle Multi-Table Association UPDATE statement 2013-12-23 17:15:49Category: OracleFor the sake of convenience, the following simple models are established and some test data are constructed:In a business admissibility subsystem BSS,SQL Code
--Customer Data sheet
Create Table Customers
(
CUSTOMER_ID Number (8) not null, --Customer indication
City_name Varchar2 (Ten) not null, --City
Custom
Recently, due to the need of the project, I have been searching for a function to update the data of another table based on the content of one table in Oracle. I haven't found a proper method on the Internet for a long time. Although
Recently, due to the need of the project, I have been searching for a function to update the data of another table based on the co
"from Zz_test2 T2 where T2." pid "=t1." ID "
)
where T1." Id ' in (SELECT ' PID ' from Zz_test2)
There is also a form of merge, the corresponding SQL is as follows:
Merge into Zz_test1 T1 using Zz_test2 t2 on (t1. " ID "=t2." pid ") when
matched then
update set T1." Text "=t2." Text1 "
To avoid having multiple data in the T2 corresponding to the data in the T1, you can change the SQL to the following ways:
MERGE into
The Oracle database update was used a few days ago, but there were a lot of problems with this simple problem. Therefore, we will search online materials and summarize them here for temporary use.
The SQL statements listed below are based on the following table:
Create table test (name varchar2 (30), code varchar2 (10), I _d varchar2 (10 ));
Insert data
Insert into test (name, code, I _d) values ('zhu1', '0
returns more than one row"
*cause:
*Action:
A comparatively simple approximation to irresponsible practice isSQL Code
Update customers a --using aliases
Set City_name= (select B.city_name from tmp_cust_city b where b.customer_id=a.customer_id and rownum=1)
How to understand the 01427 error, in a very complex multi-table connection UPDATE statement, often due to ill-conceived
reported:SQL Code
01427, 00000, "Single-row subquery returns more than one row"
*cause:
*Action:
A comparatively simple approximation to irresponsible practice isSQL Code
Update customers a --using aliases
Set City_name= (select B.city_name from tmp_cust_city b where b.customer_id=a.customer_id and rownum=1)
How to understand the 01427 error, in a very complex multi-table connection
1. Issue backgroundAlthough we do not advocate changing the primary key in database operations, we do have this business requirement in real life:表A有主键KA,表B中声明了一个references A(KA)的外键约束。我们须要改动A中某条目KA的值而且更新B中外键约束。However, the DBMS checks for integrity after running the first article update to find a conflict:the foreign key for the purpose of B does not exist .Note: I have encountered this problem in Oracle da
Oracle does not lock data if only select is used in Oracle. When the data is being modified by another process, the results obtained by using Select are not up to date.This requires a for update, and when Oracle finds that a record that satisfies the condition is being updated, it does not execute the SELECT statement
OverviewIn daily life, our use of the for update is still relatively common, especially in the manual modification of data in the case of PL/SQL developer.It is only convenient at this point, and the true meaning of the for update lacks understanding:The for update is a special case of Oracle's manual increase in lock level and range, and Oracle's lock mechanism
I want to use the Oracle database to update data when I was building an ASP. NET Website. The fields in the table include int, string, and date. NET front-end controls always encounter problems when binding updates. I checked a lot of things online and always looked at the ambiguity. After all, it was the first time I came into contact with Oracle. I updated my i
Just transferred from the Mysql/mssql database to Oracle, the update was unsuccessful when called in C #, and was later found to be a problem with incorrect format of the pass parameter.The command in Oracle is preceded by a colon: callout in front of the parameter name. Both SQL Server and MySQL use @. See Http://www.codeproject.com/Questions/618582/ORA-00936-mi
Java is a cross-platform software design language. Oracle Java 7 provides environment requirements for some programs. Oracle released Java 7 Update 40 (7u40) and WebUpd8 Java PPA was also updated, you can easily install Oracle Java 7 Update 40 (7u40) and support Ubuntu and d
Oracle Foreign keys only have cascade deletion and no Cascade update, but sometimes such functions may be required in the system, so the implementation process is recorded here.
Oracle Foreign keys only have cascade deletion and no Cascade update, but sometimes such functions may be required in the system, so the impl
In many cases, you need to reference data other than the table to be updated in the expression. Like SQL server provides the update from clause, it can connect the table to be updated with other data sources. Although only one table can be updated, by connecting the table to be updated with other data sources, you can reference data other than the table to be updated in the update expression.
For example:
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.