Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Data Manipulation Language

0
votes
2answers
42 views

Update data from one table to another in sql server

I have a log table where accidentally one date column got updated with current date for 9 Million records. Luckily I had a backup table, so now I want to populate data from backup table to current ...
10
votes
1answer
865 views

Trigger firing despite no rows being affected

This is more of a general question but the motivation for this question was a problem I faced while using SQL Server. I have this trigger attached to an Insert event on a table that contains some ...
0
votes
0answers
23 views

ORA-6502 not being caught by Oracle DML Error logging

I have added DML Error Logging while inserting records into a table. INSERT INTO <table_name> SELECT column_name ... FROM TABLE(function_name()) LOG ERRORS INTO <...
0
votes
1answer
33 views

Audit specific information [duplicate]

How can I get logon events from audit table for Feb, June, Oct and Dec? I tried the query below, but it retrieves a lot of duplicate rows although I am using the distinct keyword in the query. Also ...
0
votes
1answer
48 views

Why insert in with is returning unexpected results?

Context Schema setup create table users( id serial primary key ); Query 1 with t1 as ( insert into users default values returning id ), t2 as ( insert into users default values ...
0
votes
0answers
17 views

In SBR, why isn't there a way to restrict replication-unsafe constructs?

I was looking at the docs for MySQL 5.7 The article lists a series of built-in functions that are not safe for replication, and there's also a few examples of queries that are non-deterministic and ...
1
vote
0answers
34 views

Compute bytes count occupied by surviving cells of a table [closed]

Let there be a table that grows to 5MiB. After being VACUUMed let there be about 2MiB worth of DELETEd rows that is available for data in that table before starting storage claims to the operating ...
3
votes
1answer
84 views

PostgreSQL event trigger to force extension installations into particular schema

I have a dedicated schema extensions (PostgreSQL v 9.6.3) where I intend to install all extensions for my DB. I would like to somehow disallow (or at least remind) users installing extensions without ...
1
vote
1answer
3k views

How to Combine Insert, Update, and Delete in the same `Instead Of` Trigger, in Oracle db?

When creating an Instead Of trigger, Sql Developer allows to include all 3 DML actions in the same trigger: The result SQL looks like: CREATE OR REPLACE TRIGGER TRIGGER1 INSTEAD OF DELETE OR INSERT ...
3
votes
2answers
355 views

How can I find a view column's base table column?

I program, administer, and support an ERP application. One of my more common tasks is to understand where data originates, finding the link between a particular report field and its data entry field ...
0
votes
1answer
43 views

Need a better way to insert non-existent values in referenced table during insert/update in referencing one

I'm looking for the best way to insert data into MariaDB with JDBC. Please take a look at the following schema CREATE TABLE color ( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(...
3
votes
1answer
83 views

How to show when loan was paid off

First time posting but have been a reader for over 10 years. I have a need to show when a loan was paid off regardless of the rolling balance from refinancing/taking out additional loans. In the ...
0
votes
1answer
194 views

Capture the Query Text of a DML Change

It may not be possible, but without doing a JOIN from the inserted/updated from the records coming in and adding them to an audit table column-by-column, is it possible to just capture the full DML ...
0
votes
1answer
59 views

What is dml unplanned request?

Can someone please explain me what an unplanned DML request is? According to the book I read, an unplanned request is an ad hoc query, a request for which the need was not seen in advance. Can you ...
1
vote
0answers
203 views

PostgreSQL DML statements

I am trying to trick an application into executing a DML statement AFTER starting a SELECT statement without using stacked queries so I cannot use the ; character anywhere in my query. I have resorted ...
2
votes
1answer
2k views

SQL Server After Insert trigger does not perform actions

I have a problem with 2 After Insert triggers and 1 Instead Of Insert trigger. The After Insert triggers should perform some actions but that actions are not fired. When the Instead Of trigger fires,...
2
votes
1answer
686 views

How to audit all DML operations on a SQL Server 2008 R2 database

In order to assess options of implementing indexed views, I want to check the usage of DML operations of the tables. The database is new to me as well as the relevant business knowledge, so I just ...
0
votes
1answer
189 views

Why we can FLASHBACK DROP table where it is DDL command and hence autocommited?

DROP and TRUNCATE both are DDL commands and both are autocommited.Then why we can recover DROP table by using FLASHBACK(unless it is purge) and why we can not recover TRUNCATED data(don't consider ...
1
vote
4answers
3k views

T-SQL insert value based on identity column

Is it possible to have a table with PK based on Identity columns? Lets say I have a table of customers, two fields are important for our case Id which is PK nvarchar(20) and SequenceId which is int ...
0
votes
1answer
90 views

Oracle Conditional sum

This question was born in my mind looking at this kind of code: select sum(value) into positive from my_table where value > 0; select sum(value) into negative from my_table where value ...
1
vote
1answer
278 views

What is the query that updates the Column in a table

I have a column in a table that I don't know who is updating it . The table name is dbo.Events and the column name is [Status] and this column values are 1 or 2 . I am trying to find the query that ...
1
vote
2answers
100 views

Best approach to maintain a single set of DDLs and DMLs for multiple database type

In our application, we support multiple types of databases namely Oracle, IBM DB2 (both UDB and ZOS), postgresql and So on. Currently we are maintaining separate DDLs and DMLs for each of these ...
-2
votes
2answers
103 views

Explicit transactions for each DML query are necessary or not? [closed]

My question is simple: Which mechanism should I use in databases to ensure that if device shuts down in middle, the process is unified. transactions?
0
votes
3answers
1k views

What's faster: two UPDATEs with WHERE (in / not in) or one UPDATE with CASE?

What's best performance wise, two separate UPDATEs: UPDATE table1 SET enabled = 0 WHERE code NOT IN ( SELECT id FROM table2 ); UPDATE table1 SET enabled = 1 WHERE ...
5
votes
3answers
4k views

How to log DML statements executed by a pl/pgsql function?

I have a pl/pgsql function (see below) that lists some fields and clears their contents using dynamically constructed UPDATE commands. When I set log_statement = 'mod', I can see nothing on the log ...
0
votes
1answer
951 views

Audit DML operations on MySQL Tables

We have several MySQL 5.0 databases. We've been having security problems, like records being modified without authorization. We need to implement some kind of audit on DML operations identifying ...
1
vote
1answer
8k views

Using EXECUTE IMMEDIATE inside PL\SQL Block

Using EXECUTE IMMEDIATE inside PL\SQL block makes the whole block commit immediately. begin INSERT INTO Customer ( GUID, STATUS, NAME) VALUES (1,1,'xx'); EXECUTE IMMEDIATE 'CREATE TABLE Shop ( ...
0
votes
2answers
2k views

How to create a versioning DDL/DML change script for SQL Server?

I have an application which has a database, and some versions of the application introduce schema changes. To make installation easier (?), I figured I want to create a meta table which contains the ...
5
votes
2answers
3k views

Postgres DDL and DML best practices and coding standards [closed]

Is there a best practice or coding/design conventions for Postgres both DDL and DML/SQL that you could share and recommend. I am looking for something similar to what Google has for programming. ...
1
vote
2answers
370 views

Is it possible to validate DML statements?

Suppose that I want to delete a user named 'abc cba' from the database. So I run the following query: DELETE FROM table WHERE fname = 'abc' AND last name = 'cba'; commit; Now, there can be more ...
1
vote
1answer
557 views

Update field1.table1 based on compound join between table1 and table2 - Oracle

I want to run update statement on Oracle 11g, but it fails with the following error: SQL Error: ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery ...
14
votes
1answer
15k views

How to determine the collation of a table in PostgreSQL?

I want to script a check of the collations used on my tables in PostgreSQL, but googling for Postgresql detect collation is not working well for me, and the documentation is not making this an easy ...
7
votes
3answers
3k views

Finding delete or update records

I have had three incidents in recent months where records in a table that have either been deleted or values updated to zero across a whole table. We have a team of four people who have the ...
1
vote
1answer
1k views

DML operation against synonym oracle

I have been reading the GoldenGate 11g R2 installation guide and it states: Non-supported objects and operations in Oracle DML: [...] Synonyms [...] But I see in the next section that ...
18
votes
2answers
38k views

Is commit necessary after DML operation in Function/Procedure?

I wonder to know if it is necessary to write commit after insert/delete/update in function/procedure? Example: create or replace function test_fun return number is begin delete from a; return ...
9
votes
1answer
16k views

Why would an UPDATE SET REPLACE() statement match rows, but change none and give no warnings?

I'm seeking a certain string in a field and want to replace it with a new string. Specifically, I want all references to one URL to be changed to another URL. I've crafted this SQL statement and am ...
4
votes
2answers
6k views

Does the SELECT statement count into DML?

Looking at the name Data Manipulation Language (DML) I would assume, that all contained statements are actually for manipulating data. As far as I know the SELECT statement can only be used to query ...
2
votes
1answer
363 views

MySQL Update: why would rows matched change based on the set clause?

I ran an update query joining across a huge number of tables that updated one field, and it matched 29k some-odd rows. Running the same query with only an addition in the Set clause increased the ...
6
votes
5answers
4k views

How different are the top DB vendor's SQL (DML-) dialects from each other?

Does someone who has worked extensibly SQL-wise with at least two top DB products (such as Oracle, SQL Server, Informix, Sybase, DB2, Teradata) know how different the DB vendor's SQL dialects are from ...