天道酬勤,学无止境

dml

hibernate update single column using criteria

Question I have a table that contains mamy columns and I want to update the one or few columns of the row without effecting remaining columns I can write query: update table as t set t.a=:a set t.b=:b where t.id=1 But seen I dont know which columns will be selected to update, and I think it is not a good idea to write every query for every scenarios. Well, I have to write query for every scenarios, but I am looking for a better way to update the table dynamically. I am thinking criteria would be a good choice. But the problem is that I have no idea how to write criteria update specific column

2021-09-14 14:01:17    分类:技术分享    hibernate   hibernate-criteria   dml

Dynamically replacing the value of a node in XML DML

Question I am struggling with this now: How do you replace the value of a node in an untyped XML column where the text is equal to a certain variable value? Is it possible? My XML: <attrs> <attr>ManualInsert</attr> <attr>ManualInsert2</attr> <attr>ManualInsert4</attr> <attr>ManualInsert8</attr> </attrs> My Tries: DECLARE @OldValue Varchar(255) = 'ManualInsert' DECLARE @NewValue Varchar(255) = 'ReplacedValue' UPDATE Labels SET Attributes.modify('replace value of (/attrs/attr/text())[1] with if ((/attrs/attr/text() = sql:variable("@OldValue"))) then sql:variable("@NewValue") else () ') WHERE Id

2021-08-11 10:38:54    分类:技术分享    sql-server   xml   tsql   xquery   dml

SQL DML: Incorrect date value (MySQL)

Question I created a table in my database: CREATE TABLE official_receipt( student_no INT UNSIGNED, academic_year CHAR(8), trimester ENUM('1', '2', '3'), or_no MEDIUMINT UNSIGNED, issue_date DATE NOT NULL, received_from VARCHAR(255) NOT NULL, amount_of DECIMAL(8,2) NOT NULL, issued_by VARCHAR(255), doc_type ENUM('FULL', 'DOWN', 'INST') NOT NULL, form_of_payment ENUM('CASH', 'INST') NOT NULL, PRIMARY KEY (student_no, academic_year, trimester, or_no) ); I inserted some values: INSERT INTO official_receipt(student_no , academic_year, trimester, or_no, issue_date, received_from, amount_of, issued

2021-07-13 01:53:02    分类:技术分享    mysql   insert-into   date-format   dml

Is using a SELECT inside a pipelined PL/SQL table function allowed?

Question The docs for pipelined functions say that DML is not allowed when they are used in a SQL statement (typically a SELECT), and in most examples the pipelined functions are used for data generation or transformation (accepting a custor as parameter), but not issuing any DML statements. Now, technically, it is possible to use SELECTs without any error from Oracle (ORA 14551 will not occur). However, I have experiences reproducible strange behavior of the select; even though PRAGMA AUTONOMOUS_TRANSACTION is not being used, the rows retrieved by the SELECT seem not always taking the current

2021-07-11 16:30:40    分类:技术分享    sql   oracle   plsql   dml   pipelined-function

Combine stored procedure and query in T-SQL

Question How do I combine executing of a stored procedure and using its result or parameters in a regular SQL query? For example I would like to do something like the following: -- passing result of SELECT to SP SELECT a, b FROM t EXEC my_sp a, b -- passing result of SP to INSERT INSERT INTO t EXEC my_sp a, b etc. Answer1 no, you need to use a temp table create table #results (col1 int, col2 varchar(5) ...) INSERT INTO #results EXEC YourProcedure @parma... then you can join to it SELECT * FROM YourTable y JOIN #results r ON ... .... if you don't know the columns and data types from the

2021-06-12 11:01:08    分类:技术分享    sql   sql-server   tsql   stored-procedures   dml

What are DDL and DML?

Question I have heard the terms DDL and DML in reference to databases, but I don't understand what they are. What are they and how do they relate to SQL? Answer1 The following is adapted from here MySQL What is DDL, DML and DCL?: DDL DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database. CREATE – to create database and its objects like (table, index, views, store procedure, function and triggers). ALTER – alters the structure of the existing database. DROP – delete objects from the database. TRUNCATE –

2021-05-31 17:50:56    分类:技术分享    sql   ddl   dml

DDL/DML/DCL difference

DDLOverview of DDL DDL (Data Definition Language) is used to manipulate objects and object attributes. Such objects include the database itself, as well as database objects, such as tables, views, etc. The management and definition of these objects and attributes by DDL are specifically manifested in Create, Drop, and Alter. Special attention: The concept of "object" in DDL operation, "object" includes objects and their attributes, and the smallest object is also a level larger than the record. Take the table as an example: Create creates a data table, Alter can change the fields of the table

2021-05-13 21:08:50    分类:博客    ddl   dml

DML(data manipulation language)

SQL21、DML(data manipulation language) DML statement database manipulation language: insert data INSERT, delete data DELETE, update data UPDATE Insert data INSERTINSERT INTO table name VALUES (value 1, value 2, value 3...value n); --complete insert INSERT INTO table name (column name, column name) VALUES (value 1, value 2);-partial insertUpdate data UPDATEUPDATE table name SET column name = value WHERE CONDITION; — Syntax update t6 set name='cc' where id=2; update mysql.user set authentication_string=password(“QianFeng@123456”) where user=“root”; – change the database password flush privileges

2021-05-13 13:08:43    分类:博客    dml

SQL language insert data, modify data and delete data

Ø The data update statement DML of SQL language mainly includes three statements: insert data, modify data and delete data. 4.1.1 Insert data record Insert a new record ØThe syntax format is: INSERT INTO < table name >[(< column name1 >[,< column name2>…])] VALUES(< value >) ØExercise : Insert a student record in the S table (student number: S7 ; name: Zheng Dong; gender: female; age: 21 ; department: computer). reference: INSERT INTO S VALUES ('s7',' Zheng Dong ',' female ',21,' computer ') note: Ø Each data must be separated by a comma, and character data must be enclosed in single quotation

2021-05-11 14:59:18    分类:博客    sql   dml   插入数据   SQL Server

[DB written test interview 658] In Oracle, what are the functions of the DML_LOCKS and DDL_LOCK_TIMEOUT parameters?

♣ 题目 section In Oracle, what are the functions of the DML_LOCKS and DDL_LOCK_TIMEOUT parameters? ♣ 答案部分 The total number of TX locks is determined by the initialization parameter TRANSACTIONS , and the number of TM locks is determined by the initialization parameter DML_LOCKS . The DML_LOCKS parameter is a derived parameter, DML_LOCKS=4 * TRANSACTIONS . Before Oracle 11g , DDL statements would not wait for DML statements. When the object accessed by the DDL statement is executing the DML statement, an error " ORA-00054: resource busy and acquire with nowait specified " will be reported

2021-04-20 01:35:58    分类:博客    oracle   dml