Oracle - UPDATE

oracle

How can we update the values of a column while keeping one part and replacing the other part?

UPDATE tableName SET columnName = SUBSTR(columnName,1,INSTR(columnName,'@')-1) || 'replacementPart' 
WHERE UPPER(columnName) LIKE UPPER('%@%');

One of my customer needed to change their email addresses (just the domain portion of the email address) because they merged with another company. This customer has 900+ users in my system. Changing email address for each individual user was not an option.

UPDATE schemaName.tableName
   SET columnNameA =
          REPLACE (
             columnNameA,
             ' WHERE IN THE WORLD IS CARMAN SAN DIEGO - NEED LONG NAME',
             '')
 WHERE columnNameB IN (SELECT columnNameC
                        FROM schemaName.tableName
                       WHERE columnNameD = 555001881);
UPDATE schemaName.tableName SET columnName=REPLACE(columnName, 'search string', 'substitution string') WHERE ...;

The above SQL uses the REPLACE function.

How can we update a table using data from another table?

This statement use a dependent subselect:

UPDATE schemaName.tableName_PROJECTS A SET A.ADDDATE = (SELECT MIN(B.ADDDATE) FROM schemaName.tableNameB B 
WHERE B.ENTITIESID=A.ENTITIESID GROUP BY B.ENTITIESID) WHERE A.ADDDATE IS NULL AND A.PROJECTSID=802;
UPDATE 
(SELECT table1.value as OLD, table2.CODE as NEW
 FROM table1
 INNER JOIN table2
 ON table1.value = table2.DESC
 WHERE table1.UPDATETYPE='blah'
) t
SET t.OLD = t.NEW

The SQL statement below assign values to multiple columns at once using a sub-select:

UPDATE schemaName.tableName
   SET (DISCLAIMER_LABEL,
        DISCLAIMER_TEXT,
        PUNITIVE_LABEL,
        PUNITIVE_TEXT,
        HIPPA_LABEL,
        HIPPA_TEXT) =
          (SELECT DISCLAIMER_LABEL,
                  DISCLAIMER_TEXT,
                  PUNITIVE_LABEL,
                  PUNITIVE_TEXT,
                  HIPPA_LABEL,
                  HIPPA_TEXT
             FROM schemaName.tableName
            WHERE columnName = 50010)
 WHERE columnName IN
          (SELECT columnName
             FROM schemaName.tableName
            WHERE columnNameA = 555001881 OR columnNameB = 555001881);
UPDATE schemaNamel.tableNameA a
  SET a.otlabel =
         (SELECT b.label
            FROM schemaName.tableNameB b
           WHERE a.ot_id = b.ot_id);

The above SQL statement, table a is from the outer statement and table b is from the inner / sub-select statement.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License