Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

UPDATE/INSERT from a full data table to an empty table.

994772Mar 21 2013 — edited Mar 21 2013
Hello 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.
This post has been answered by BluShadow on Mar 21 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 18 2013
Added on Mar 21 2013
4 comments
287 views