Rowlock with inner join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Rowlock with inner join

Author

Topic

issammansour
Yak Posting Veteran

51 Posts

Posted - 2007-10-26 : 06:06:29

Him

I am trying to issue the following statement to lock a record with inner join but I have an error:-

SELECT amaster.acctno,bmaster.balance,bmaster.YEAR
FROM amaster [ROWLOCK] with (Xlock)
INNER JOIN bmaster
ON amaster.acctno = bmaster.acctno
WHERE (bmaster.YEAR = 2007) acctno = 10000100
ORDER BY amaster.acctno

The error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "amaster.acctno" could not be bound.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 06:14:25

That's because you have aliased the amaster table with the name rowlock.

E 12°55'05.25"
N 56°04'39.16"

issammansour
Yak Posting Veteran

51 Posts

Posted - 2007-10-26 : 06:40:01

no I don't have any aliased with same name

Kristen
Test

22859 Posts

Posted - 2007-10-26 : 06:49:33

What Peso means is that you will have to use the names of the Alias you provided, instead of the table name:


SELECT [ROWLOCK].acctno,bmaster.balance,bmaster.YEAR
FROM amaster [ROWLOCK] with (Xlock)
INNER JOIN bmaster
ON [ROWLOCK].acctno = bmaster.acctno
WHERE (bmaster.YEAR = 2007) acctno = 10000100
ORDER BY [ROWLOCK].acctno

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 07:06:23

quote:
Originally posted by issammansour

SELECT amaster.acctno,bmaster.balance,bmaster.YEAR
FROM amaster [ROWLOCK] with (Xlock)
INNER JOIN bmaster
ON amaster.acctno = bmaster.acctno
WHERE (bmaster.YEAR = 2007) acctno = 10000100
ORDER BY amaster.acctno

YES YOU HAVE!
You have told the query processor to "rename" the amaster table to [rowlock] table.

Try this and you will see that it work!

SELECT [ROWLOCK].acctno, bmaster.balance, bmaster.YEAR
FROM amaster [ROWLOCK] with (Xlock)
INNER JOIN bmaster
ON [ROWLOCK].acctno = bmaster.acctno
WHERE (bmaster.YEAR = 2007) and acctno = 10000100
ORDER BY [ROWLOCK].acctno

I have also put the red AND as completeness to your query...

E 12°55'05.25"
N 56°04'39.16"

Ouch me

issammansour
Yak Posting Veteran

51 Posts

Posted - 2007-10-26 : 08:22:59

No sir, the select statement not working and there is no table with rowlock, but I still need help with a rowlock and inner join. thanks

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2007-10-26 : 08:40:36

the point is that whilst you may not have intended to alias the amaster table, the syntax you've used (...from amaster [rowlock] ...) means that is what you've done.

can you describe what you are actually trying to acheive?

Em

Kristen
Test

22859 Posts

Posted - 2007-10-26 : 09:36:05

"the select statement not working"

Did you TRY to code that Peso posted?

If there is an error message we need to know what it is, otherwise we are only guessing what the problem is you want us to help fix.

Kristen

richard75013
Starting Member

19 Posts

Posted - 2007-10-26 : 14:43:53

quote:
Originally posted by issammansour

No sir, the select statement not working and there is no table with rowlock, but I still need help with a rowlock and inner join. thanks

issammansour,
It would be much easier if you just get a box and send the computer back, they are right and are trying to help you but you have to be receptive to others when you ask for help. :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-26 : 14:49:23

quote:
Originally posted by richard75013

quote:
Originally posted by issammansour

No sir, the select statement not working and there is no table with rowlock, but I still need help with a rowlock and inner join. thanks

issammansour,
It would be much easier if you just get a box and send the computer back, they are right and are trying to help you but you have to be receptive to others when you ask for help. :)

ROTFLMAO! I got a big chuckle out of this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources