Oracle - UPDATE
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.
page revision: 9, last edited: 25 Nov 2015 16:20