Help with INSERT INTO 1

[SelbyGlenn]

Hi there,

I have two tables: APPMAIN and APPPERSONAL. Both have identical AppCode columns in them. I need to copy Course and YearGrp from APPPERSONAL to CourseId and CourseYear in APPMAIN. Here's what I have so far:

Insert INTO APPMAIN (CourseId, CourseYear) SELECT from APPPERSONAL (Course, YearGrp) WHERE APPMAIN.AppCode = APPPERSONAL.AppCode

it returns error: Incorrect syntax near the keyword 'from'

Any ideas where I'm going wrong?

Thanks in advance,

Glenn
BEng MCSE CCA

 

[SelbyGlenn]

Ok I've spotted one mistake. the line should read:

Insert INTO APPMAIN (CourseId, CourseYear) SELECT Course, YearGrp from APPPERSONAL WHERE APPMAIN.AppCode = APPPERSONAL.AppCode

But now I'm getting error:

The column prefix 'APPMAIN' does not match with a table name or alias name used in the query



Glenn
BEng MCSE CCA

 

[JamesLean]

It sounds like you need to UPDATE rather than INSERT:

UPDATE appmain
SET courseid = p.course,
  courseyear = p.yeargrp
FROM appmain m JOIN apppersonal p ON m.appcode = p.appcode

--James

 

[PHV]

You may try this:
UPDATE AppMain M INNER JOIN AppPersonal P ON M.AppCode = P.AppCode
SET M.CourseId = P.Course, M.CourseYear = P.YearGrp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SelbyGlenn]

James that worked a treat!

I'm pretty new to SQL but am I right in assuming that m and p are abbreviations which you have declared after appmain and apppersonal?

Glenn
BEng MCSE CCA

 

[JamesLean]

Yes - they are called aliases.

--James

 

[SelbyGlenn]

Clever stuff.

Have a star on me!

Thanks for you help

Glenn
BEng MCSE CCA