Structured Query Language (SQL) is a standard computer language for relational database management and data manipulation. SQL is used to query, insert, update and modify data
I admit it. I suffered from an “algebra narcoleptic syndrome” during my relational database lessons at University (1996 circa). Ok it is a fake. But it seems so real on these days. I was unable to avoid sleeping. I learned to love SQL after some years and now I think SQL is the most powerful, …
SQL: One of the most valuable skills – Craig Kerstiens Read More »
For a complete description see https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
It aims to provide a minimal compatibility with Oracle. The need was having some regular expression functions, and I do not like to work with risky C code, like this university project did
Sometimes you need to remove nasty duplicate on a table, based on a subset of the column. Oracle trick:
|
1 2 3 4 5 |
DELETE FROM your_table WHERE rowid not in (SELECT MIN(rowid) FROM your_table GROUP BY column1, column2, column3); |
Postgresql equivalent is ctid, so you can try something like
|
1 2 3 4 5 6 7 8 |
delete from mytable where exists (select 1 from mytable t2 where t2.name = mytable.name and t2.address = mytable.address and t2.zip = mytable.zip and t2.ctid > mytable.ctid ); |
SQLite demo:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
create table dup_table (column1 text, column2 text, column3 text); insert into dup_table values('1','2','3'); insert into dup_table values('1','2','3'); insert into dup_table values('1','3','3'); DELETE FROM dup_table WHERE rowid not in (SELECT MIN(rowid) FROM dup_table GROUP BY column1, column2); select count(*) from dup_table; |
Also take a look at this old post on postgresql mailing list, if you incur in an older PostgreSQL version …
Removing duplicate rows in PostgreSQL, Oracle and SQLite Read More »
In Italian language we have accent words like àèéìòù They are quite important because for instance “is” is spelled è whereas “and” is spelled e Accent are bad guys even today: if you copy them from MS-Word inside an Oracle sql script you can end up with different UTF-8 values. Also the accent are likely …
On some environment, on some customers, Oracle connections are drop after some idle time (i.e. 2 minutes). I have found a small plugin for Sql Developer to solve this issue: it is very handy and logs its usage. Code is also very clean keepalive extension for Oracle SQL Developer 4+https://github.com/scristalli/SQL-Developer-4-keepalive9 forks.86 stars.7 open issues.Recent commits: Fix …
Oracle SQL Developer is full of nice feature, damned by a overwhelming options pane, like the one I will describe to you right now. Even if Oracle databases (<12) does not support auto increment, you can easily ask to your sql data modeler to generate for you a sequence and a trigger in a automatic …
Oracle SQL Developer is full of nice feature, damned by a overwhelming options pane, like the one I will describe to you right now. I will show here a very fast way of comparing different database and auto-generate migration script. Reverse engineer the source database using Oracle Data Modeler Now select the right arrow shown …
I stumbled upon a very brain-f**k error on Oracle 10g on these days. Context: the following query [sql]SELECT * FROM ( SELECT TO_NUMBER(CUSTOMER_ID) AS SNDG FROM BAD_CODES_TABLE WHERE AND I_LIKE=UPPER(‘STATIC_CONDITION’) AND CUSTOMER_ID NOT LIKE ‘%P%’ ) S WHERE TO_NUMBER(S.SNDG) >2000[/sql] could trigger a Invalid number if CUSTOMER_ID column contains invalid numbers. Why? Well…if you ask …
Come evitare iniezione SQL: lato SQL Server (SP_EXECUTESQL) In generale va evitato nel modo più assoluto la scrittura di query sql diamiche. Va evitato cioè l’uso lato SQL Server di sp_executesql e EXEC Di seguito mostriamo come trasformare una query “dinamica” in una “statica” [sql]CREATE PROCEDURE search_orders @custid nchar(5) = NULL, @shipname nvarchar(40) = …
Come evitare iniezioni SQL lato DB in SQLServer, Oracle, MySQL Read More »
