Data Manipulation Language

learn more… | top users | synonyms

7
votes
1answer
4k 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 ...
7
votes
2answers
1k 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 ...
5
votes
1answer
2k 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 ...
4
votes
2answers
9k 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 ...
3
votes
4answers
1k 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 ...
2
votes
2answers
1k 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
273 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 ...
1
vote
2answers
92 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
3answers
47 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 ...
1
vote
1answer
219 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 ...
1
vote
1answer
309 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 ...
0
votes
1answer
31 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 ...
0
votes
1answer
292 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
1answer
126 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 ...