Help with inner join

[rwies]

I am a new sql 2000 user/administrator. I am just learning the programming syntax. I am trying to do and insert function to take rows from one table and add them to another table where certain conditions are met. Each of the two tables have an autonumber key field named zipdef_no. I have tried to set up an innerjoin that references this common condition. Below is the code I have written:

use montrain

insert into dbo.zip_default_agency
(zipdef_no, agency_no, agencytype_id, change_user, change_date)

select zipdef_no, agency_no, agencytype_id = 'P', change_user = 1, change_date = getdate

from dbo.zip_default INNER JOIN
dbo.zip_default.zipdef_no = dbo.zip_default_agency.zipdef_no

where (left(city_name,5) = 'Tulsa')

When I execute the code, I get the following error:

Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near '='.

I don't understand what the problem may be. Any help is greatly appreciated.

Thanks,

Ron--

 

[hmckillop]

try

insert into dbo.zip_default_agency
(zipdef_no, agency_no, agencytype_id, change_user, change_date)

select zipdef_no, agency_no, agencytype_id = 'P', change_user = 1, change_date = getdate

from dbo.zip_default zip1
INNER JOIN dbo.zip_default_agency zip2 ON zip1.zipdef_no = zip2.zipdef_no

where (left(city_name,5) = 'Tulsa')

"I'm living so far beyond my income that we may almost be said to be living apart

 

[jby1]

Your inner join syntax is incorrect. the correct way is something like

select t1.field1, t2.field2
from table1 t1
INNER JOIN table2 t2 ON t1.keyfield = t2.keyfield

On each side of the INNER JOIN must be the names of the table you are joining, then you specify the actual join fields after the ON statement.

 

[jby1]

hmckillop's answer is the actual way you would apply what i have said in your case!

 

[hmckillop]

A good point by jby1 that I missed was to qualify the fields with the actual table alias.
so in my example use zip1 or zip2 prior to the fields in the SELECT to ensure the correct information is selected from the correct table (though you will receive an error if the fieldname exists across the two tables)


"I'm living so far beyond my income that we may almost be said to be living apart

 

[r937]

folks, it's not getdate, it's getdate()



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[hmckillop]

good spot!

"I'm living so far beyond my income that we may almost be said to be living apart