No Inner Joins?

Hello:

I'm relatively new to MSQL - I come from a MySQL background and have a crap load of experience with Access/Jet. I tried executing a query in MSQL last night, and got an error. Here's my query:

update inv_mast inner join inv_loc on inv_mast.inv_mast_uid = inv_loc.inv_mast_uid SET inv_mast.short_code = 'Entrelec' WHERE inv_loc.primary_supplier_id = '100086'

I got the error:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'inner'.

So I tried changing the syntax to:

UPDATE inv_mast
SET inv_mast.short_code = 'Entrelec'
FROM inv_mast, inv_loc
WHERE inv_mast.inv_mast_uid = inv_loc.inv_mast_uid
AND inv_loc.primary_supplier_id = '100086'

And didn't get an error, but I didn't get any results (0 row(s) affected).

What am I doing wrong?

If my queries don't give enough of an explanation of the layout of the data, let me know, I'll gladly explain more.

Thanks all! :)

-jimyour update statment probably should have looked like:
update inv_mast
SET inv_mast.short_code = 'Entrelec'
from inv_mast
inner join inv_loc on inv_mast.inv_mast_uid = inv_loc.inv_mast_uid
WHERE inv_loc.primary_supplier_id = '100086'

and if you like aliases:
update im
SET im.short_code = 'Entrelec'
from inv_mast im
join inv_loc il on im.inv_mast_uid = il.inv_mast_uid
WHERE il.primary_supplier_id = '100086'

as to why you didn't update anything, maybe your keys don't line up or you have nothing that satisfies your where clause. what does the following produce?
select im.inv_mast_uid,il.primary_supplier_id
from inv_mast im
join inv_loc il on im.inv_mast_uid = il.inv_mast_uid
order by 1,2|||Paul,

Thanks a TON for your response! The last query you posted lists all the item master ID's with their vendor IDs. So I guess the keys are good.

I'm going to try your update query now...

Dude - I owe you big time!! It worked like a charm - how can I thank you?

-jim

no count of zero

Count doesn't seem to be returning zero when there are no records. I've
checked my joins. I've tried ISNULL(Count(*), 0) [which I've NEVER had to do
before] to handle a null count (still no 0). It's a new machine to me, and
I'm wondering if there is some system setting I need to make sure count
returns a zero when no records are returned.
I can switch to returning @.@.RowCount value, but that's not what I'm trying
to do.
Any help?
Thanks
--
Buz WaitzPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
You might want to look up the behavior of an empty table and aggregate
functions; COUNT(*) and COUNT(<exp> ) are not the same.|||Buz (bwaitz@.minormiracles.com.nospamplease) writes:
> Count doesn't seem to be returning zero when there are no records. I've
> checked my joins. I've tried ISNULL(Count(*), 0) [which I've NEVER had
> to do before] to handle a null count (still no 0). It's a new machine to
> me, and I'm wondering if there is some system setting I need to make
> sure count returns a zero when no records are returned.
Better if you post the code you are having problem with. Else we are
completely in the dark of trying to understand what you are trying to do.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Found it. It was a group by clause that caused the problem.
Thanks,
--
Buz Waitz
"--CELKO--" wrote:

> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> You might want to look up the behavior of an empty table and aggregate
> functions; COUNT(*) and COUNT(<exp> ) are not the same.
>