Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 522
  • Last Modified:

substring join

I am trying to use substring commands while joining 2 tables.  I am getting the following error.  I am not sure if it is because of the set command or something different.  Here is the command

UPDATE TEMP_ENROLL
SET SECTION_ID = set SECTION_id =  substring('S_',1,2) + RTRIM(SECTION_id.class_subject_cd) + RTRIM(SECTION_ID.CLASS_CATALOG_NBR) + RTRIM(SECTION_ID.CLASS_CLASS_SECTION)
FROM SECTION_ID
WHERE CLASS_NBR = CLASS_CLASS_NBR

any help is appreciated.

Thanks!
0
ericsberr
Asked:
ericsberr
  • 3
  • 3
  • 2
  • +4
1 Solution
 
NightmanCTOCommented:
UPDATE TEMP_ENROLL
SET SECTION_ID = substring('S_',1,2) + RTRIM(SECTION_id.class_subject_cd) + RTRIM(SECTION_ID.CLASS_CATALOG_NBR) + RTRIM(SECTION_ID.CLASS_CLASS_SECTION)
FROM SECTION_ID
WHERE CLASS_NBR = CLASS_CLASS_NBR
0
 
mherchlCommented:
try this:

UPDATE TEMP_ENROLL
SET SECTION_ID = SECTION_id =  substring('S_',1,2) + RTRIM(SECTION_id.class_subject_cd) + RTRIM(SECTION_ID.CLASS_CATALOG_NBR) + RTRIM(SECTION_ID.CLASS_CLASS_SECTION)
FROM TEMP_ENROLL join SECTION_ID on CLASS_NBR = CLASS_CLASS_NBR
0
 
Michael_DCommented:
Try
UPDATE TEMP_ENROLL
SET SECTION_ID =  substring('S_',1,2) + RTRIM(SECTION_id.class_subject_cd) + RTRIM(SECTION_ID.CLASS_CATALOG_NBR) + RTRIM(SECTION_ID.CLASS_CLASS_SECTION)
FROM SECTION_ID
WHERE CLASS_NBR = CLASS_CLASS_NBR


Seems like you have double = set SECTION_id in your code
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Aneesh RetnakaranDatabase AdministratorCommented:
UPDATE t
SET SECTION_ID = substring('S_',1,2) + RTRIM(class_subject_cd) + RTRIM(CLASS_CATALOG_NBR) + RTRIM(CLASS_CLASS_SECTION)
FROM SECTION_ID
INNER JOIN TEMP_ENROLL t
ON CLASS_NBR = CLASS_CLASS_NBR
   
0
 
Scott PletcherSenior DBACommented:
Don't think I fully understand, but maybe this:

UPDATE TEMP_ENROLL
SET SECTION_ID = 'S_' + RTRIM(SECTION_id.class_subject_cd) + RTRIM(SECTION_ID.CLASS_CATALOG_NBR) + RTRIM(SECTION_ID.CLASS_CLASS_SECTION)
FROM TEMP_ENROLL
INNER JOIN SECTION_ID ON CLASS_NBR = CLASS_CLASS_NBR
0
 
srafi78Commented:
UPDATE TEMP_ENROLL
SET SECTION_ID  = (Select substring('S_',1,2) + RTRIM(SECTION_id.class_subject_cd) + RTRIM(SECTION_ID.CLASS_CATALOG_NBR) + RTRIM(SECTION_ID.CLASS_CLASS_SECTION)
FROM SECTION_ID
WHERE CLASS_NBR = CLASS_CLASS_NBR)
0
 
ericsberrAuthor Commented:
I tried many of these (i think all of them) the message I am getting is
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Class_nbr'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Class_nbr'.

Is that helpful?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
UPDATE t
SET SECTION_ID = substring('S_',1,2) + RTRIM(class_subject_cd) + RTRIM(CLASS_CATALOG_NBR) + RTRIM(CLASS_CLASS_SECTION)
FROM SECTION_ID
INNER JOIN TEMP_ENROLL t
ON t.CLASS_NBR = CLASS_NBR
0
 
ericsberrAuthor Commented:
Thanks aneeshattingal  buti am still getting this error..

Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Class_nbr'.
0
 
Michael_DCommented:
UPDATE TEMP_ENROLL
SET TEMP_ENROLL.SECTION_ID = substring('S_',1,2) + RTRIM(SECTION_ID.class_subject_cd) + RTRIM(SECTION_ID.CLASS_CATALOG_NBR) + RTRIM(SECTION_ID.CLASS_CLASS_SECTION)
FROM SECTION_ID
INNER JOIN TEMP_ENROLL
ON TEMP_ENROLL.CLASS_NBR = SECTION_ID.CLASS_NBR
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
can u provide the table structure,      



UPDATE t
SET SECTION_ID = substring('S_',1,2) + RTRIM(s.class_subject_cd) + RTRIM(s.CLASS_CATALOG_NBR) + RTRIM(s.CLASS_CLASS_SECTION)
FROM SECTION_ID S
INNER JOIN TEMP_ENROLL t
ON t.CLASS_NBR = S.CLASS_NBR
0
 
Michael_DCommented:
Thank you for points and grade :)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now