UPDATE/INSERT from a full data table to an empty table.
994772Mar 21 2013 — edited Mar 21 2013Hello all,
I am using Oracle 11g-64 bit, and the TOAD client.
I have two tables...a TMP_SUPP_CONTACTS (TMP) table and a TBL_SUPP_CONTACTS (TBL) table. The TMP table has all this "raw" data (lower case, not trimmed, etc). The TBL is completely empty, besides columns. I need to move this data from the TMP table to the TBL.
The columns in each table go like this:
TMP:
CNTCT_ID
SUPP_IDNT
FIRST_NM
MID_NM
LAST_NM
ROLE_TITLE
ADDR_LN_1
ADDR_LN_2
ADDR_LN_3
CITY_NM
STATE_CD
POST_ZN_CD
CTRY_CD
PH_NUM
EMAIL_ADR_TXT
TBL:
ORG_ID
CNTCT_ID
SUPP_CD
DATA_SOURCE
ROLE_TITLE
FIRST_NM
LAST_NM
ADDR_ST
ADDR_CITY_NM
ADDR_ZIPCD
ADDR_CNTRY_CD
PH_NUM
ADDR_EMAIL
ADD_DT
LAST_UPD_DT
I am combining from the TMP table, ADDR_LN_1 + ADDR_LN_2 + ADDR_LN_3 to ADDR_ST in the TBL. My code looks like this...
UPDATE TBL_SUPP_CONTACTS TBLSC
SET
TBLSC.ORG_ID = 'XXX',
TBLSC.CNTCT_ID = TRIM(UPPER(TMPSC.CNTCT_ID)),
TBLSC.SUPP_CD = TRIM(UPPER(TMPSC.SUPP_IDNT)),
TBLSC.DATA_SOURCE = 'E',
TBLSC.ADDR_ST = TRIM(UPPER(TMPSC.ADDR_LN_1))
|| ' '
|| TRIM(UPPER(TMPSC.ADDR_LN_2))
|| ' '
|| TRIM(UPPER(TMPSC.ADDR_LN_3)),
TBLSC.ADDR_CITY_NM = TRIM(UPPER(TMPSC.CITY_NM))
|| ' '
|| TRIM(UPPER(TMPSC.STATE_CD)),
TBLSC.ADDR_CNTRY_CD = TRIM(UPPER(TMPSC.CTRY_CD)),
TBLSC.ADDR_EMAIL = TRIM(UPPER(TMPSC.EMAIL_ADR_TXT)),
TBLSC.ADDR_ZIPCD = TRIM(UPPER(TMPSC.POST_ZN_CD)),
TBLSC.FIRST_NM = TRIM(UPPER(TMPSC.FIRST_NM)),
TBLSC.LAST_NM = TRIM(UPPER(TMPSC.LAST_NM))
|| ' '
|| TRIM(UPPER(TMPSC.MID_NM)),
TBLSC.PH_NUM = TRIM(UPPER(TMPSC.PH_NUM)),
TBLSC.ROLE_TITLE = TRIM(UPPER(TMPSC.ROLE_TITLE)),
TBLSC.LAST_UPD_DT = SYSTIMESTAMP,
TBLSC.ADD_DT = '03/06/2013'
FROM TMP_SUPP_CONTACTS TMPSC;
So , I have a few questions.
1. Why won't this code work? It's giving me an error on my FROM statement. I am new to Oracle and SQL as a whole, but am trying my best to figure this out.
2. For the TBLSC.LAST_UP_DT field...I would like to format the SYSTIMESTAMP in a MM/DD/YYYY format, is that possible to do?
Thank you to any advice I get for this problem. I appreciate it.