Click here to Skip to main content
15,917,176 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Table 1 (Course) has the following fields:

course.name (for example, Science)
course.number (for example, SC101S1)
course.id
course.calendarid

Table 2 (CustomCourse) has the following following fields:
customcourse.id
customcourse.value
customcourse.attributeid

I want to insert into customcourse.value the concatenated value of course.name + '-' + course.number from the course table

What I have tried:

I've tried an update statement but 0 rows are affected:

--Works but zero rows affected
UPDATE customcourse SET customcourse.value = name + '_' + number
from course
Inner join customcourse on customcourse.courseID = course.courseID
where course.calendarID = '162'
and course.number = 'CL03S2'
and customcourse.attributeID = '1132'


--Syntax doesn't work in INSERT INTO statement

INSERT INTO CustomCourse
Select Concat ('course.name', '_', 'course.number') AS customcourse.value
Inner join course on course.courseID = customccourse.courseID
where customcourse.attributeID = '1132'
and course.calendarID = '162'
Posted
Updated 19-Dec-17 4:54am

1 solution

Assuming customcourse.id is an IDENTITY field:
SQL
INSERT INTO customcourse
(
    value,
    attributeid
)
SELECT
    Concat(course.name, '_', course.number),
    '1132'
FROM
    course
WHERE
    calendarID = '162'
;
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900