Left join works in Access NOT in SQL 7

[Rosko2000]

This has worked for years in Access

UPDATE tbl_keeplist2
LEFT JOIN tbl_keeplist2_daily ON
tbl_keeplist2.ID = tbl_keeplist2_daily.ID

SET tbl_keeplist2.status = 'D'

WHERE tbl_keeplist2_daily.status = 'P'
OR tbl_keeplist2_daily.ID Is Null

But no luck in SQL 7 I get the following error.

Incorrect syntax near the keyword 'LEFT'.

Any ideas?

 

[vongrunt]

Different syntax. SET comes before JOINS. Try:

UPDATE     tbl_keeplist2 
SET   status = 'D'
FROM tbl_keeplist2
    LEFT JOIN tbl_keeplist2_daily ON 
    tbl_keeplist2.ID = tbl_keeplist2_daily.ID
WHERE     tbl_keeplist2_daily.status = 'P'
    OR tbl_keeplist2_daily.ID Is Null

 

[Rosko2000]

I'm now getting Invalid object name 'tbl_keeplist2_daily'.

UPDATE tbl_keeplist2
SET tbl_keeplist2.status = 'D'
FROM tbl_keeplist2
LEFT JOIN tbl_keeplist2_daily ON
tbl_keeplist2.ID = tbl_keeplist2_daily.ID
WHERE tbl_keeplist2_daily.status = 'P'
OR tbl_keeplist2_daily.ID Is Null

 

[vongrunt]

Are you sure this table exists in current database?

 

[Rosko2000]

Yes and there are records, I did notice that this table has a different Owner then the others. All tables except for two are listed with owners of DBO the others are listed with Homefinder which is the user created for that database. Would this have anything to do with it?

 

[vongrunt]

Depends on login you used to run this query. Simple check: try under full-permission login (sa/**** or equivalent); if problem disappears then you are correct.

 

[Rosko2000]

Just tried it with different users and I still get the same Invalid Object Name... thoughts?

I appreciate your persistence!

 

[vongrunt]

OK... can you access this table at all:

select *
from tbl_keeplist2_daily

Another possibility: database is case-sensitive and some caps are different (tbl_Keeplist2_daily or something).

 

[Rosko2000]

ok, figured it out.. I tried selecting the data in Query analyzer and no go on that. So I double checked and logged back in with what should be the correct user thats being called by the application server and this worked

UPDATE tbl_keeplist2
SET tbl_keeplist2.status = 'D'
FROM tbl_keeplist2
LEFT JOIN tbl_keeplist2_daily ON
tbl_keeplist2.ID = tbl_keeplist2_daily.ID
WHERE tbl_keeplist2_daily.status = 'P'
OR tbl_keeplist2_daily.ID Is Null


Thanks for your help!!

 

[SQLSister]

Try prefacing it with the ownername. Also check the permissions to see if your logins have permissions on this table. Incidentally, it is ususally a good idea to avoid permissions issues by making all objects owned by dbo. Diffent owner names get into issues with stored procedures not being able to use just exec permissions and end up needing permissions set at the table level.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[Rosko2000]

True..

Last question and I'm done for the day.

UPDATE tbl_keeplist2
SET tbl_keeplist2.status = 'R'
FROM tbl_keeplist2
RIGHT JOIN tbl_moddate ON tbl_keeplist2.ID = tbl_moddate.ID

Returns

UPDATE over nullable side of outer join query on table 'tbl_keeplist2'.


Original SQL was

UPDATE tbl_keeplist2
RIGHT JOIN tbl_moddate ON tbl_keeplist2.ID = tbl_moddate.ID
SET tbl_keeplist2.status = 'R'

 

[vongrunt]

Update over nullable side (left table in RIGHT JOIN or right table in LEFT JOIN or both in FULL JOIN) is theoretically wrong because it means updating records that may not exist. Gotta love Access query designer .

Use INNER JOIN instead.

 

[Rosko2000]

You would think Access and SQL server would be a bit closer... anywho I've learn a lot today thanks!