This is called a correlated update UPDATE table1 t1 SET (name, desc) = (SELECT t2.name, t2.desc FROM table2 t2 WHERE t1.id = t2.id) WHERE EXISTS ( SELECT 1 FROM table2 t2 WHERE t1.id = t2.id ) Assuming the join results in a key-preserved view, you could also UPDATE (SELECT t1.id, t1.name name1, t1.desc desc1, t2.name … Read more
Not only is the boolean datatype missing in Oracle’s SQL (not PL/SQL), but they also have no clear recommendation about what to use instead. See this thread on asktom. From recommending CHAR(1) ‘Y”https://stackoverflow.com/”N’ they switch to NUMBER(1) 0/1 when someone points out that ‘Y”https://stackoverflow.com/”N’ depends on the English language, while e.g. German programmers might use … Read more
The MERGE statement merges data between two tables. Using DUAL allows us to use this command. Note that this is not protected against concurrent access. create or replace procedure ups(xa number) as begin merge into mergetest m using dual on (a = xa) when not matched then insert (a,b) values (xa,1) when matched then update … Read more
From Ask Tom You should consider a schema to be the user account and collection of all objects therein as a schema for all intents and purposes. SCOTT is a schema that includes the EMP, DEPT and BONUS tables with various grants, and other stuff. SYS is a schema that includes tons of tables, views, … Read more
In Oracle, to insert multiple rows into table t with columns col1, col2 and col3 you can use the following syntax: INSERT ALL INTO t (col1, col2, col3) VALUES (‘val1_1’, ‘val1_2’, ‘val1_3’) INTO t (col1, col2, col3) VALUES (‘val2_1’, ‘val2_2’, ‘val2_3’) INTO t (col1, col2, col3) VALUES (‘val3_1’, ‘val3_2’, ‘val3_3’) . . . SELECT 1 … Read more
That syntax isn’t valid in Oracle. You can do this: UPDATE table1 SET table1.value = (SELECT table2.CODE FROM table2 WHERE table1.value = table2.DESC) WHERE table1.UPDATETYPE=’blah’ AND EXISTS (SELECT table2.CODE FROM table2 WHERE table1.value = table2.DESC); Or you might be able to do this: UPDATE (SELECT table1.value as OLD, table2.CODE as NEW FROM table1 INNER JOIN … Read more
Sometimes if the update application is abruptly stopped, then the lock remains stuck. Then running UPDATE DATABASECHANGELOGLOCK SET LOCKED=0, LOCKGRANTED=null, LOCKEDBY=null where ID=1; against the database helps. You may also need to replace LOCKED=0 with LOCKED=FALSE. Or you can simply drop the DATABASECHANGELOGLOCK table, it will be recreated.
The best and most efficient way is to catch the “table not found” exception: this avoids the overhead of checking if the table exists twice; and doesn’t suffer from the problem that if the DROP fails for some other reason (that might be important) the exception is still raised to the caller: BEGIN EXECUTE IMMEDIATE … Read more
Materialized views are disk based and are updated periodically based upon the query definition. Views are virtual only and run the query definition each time they are accessed.
There is no combination of LIKE & IN in SQL, much less in TSQL (SQL Server) or PLSQL (Oracle). Part of the reason for that is because Full Text Search (FTS) is the recommended alternative. Both Oracle and SQL Server FTS implementations support the CONTAINS keyword, but the syntax is still slightly different: Oracle: WHERE … Read more