Syntax - 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

Syntax

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-07 : 09:28:43

Any idea what the heck is wrong with the syntax in this statement? I'm trying to remove entries from Civil.dbo.receipts which do not have corresponding Receiptno entries in civil.dbo.subjects. It keeps telling me that: "Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "CIVIL.dbo.RECEIPTS" could not be bound."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 09:30:22

it seems like you dont have the field in the table. to get more idea, we need to see the exact code you're trying to run.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-07 : 09:31:25

Sorry, i thought i pasted it in there:

DELETE FROM [CIVIL].[dbo].[RECEIPTS]
WHERE [RECEIPTNO] NOT IN (SELECT s.[RECEIPTNO] FROM [CIVIL].[dbo].[SUBJECTS] s WITH (NOLOCK) WHERE S.[RECEIPTNO] = [CIVIL].[dbo].[RECEIPTS])
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 09:35:18

always use aliases to make your code tidier


DELETE r 
FROM [CIVIL].[dbo].[RECEIPTS] r
LEFT JOIN [CIVIL].[dbo].[SUBJECTS] s
ON s.[RECEIPTNO] = r.[RECEIPTS]
WHERE s.[RECEIPTNO] IS NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 09:36:49

or your original suggestion modified(i prefer joins hence last suggestion)


DELETE r FROM [CIVIL].[dbo].[RECEIPTS] r
WHERE r.[RECEIPTNO] NOT IN (SELECT s.[RECEIPTNO] FROM [CIVIL].[dbo].[SUBJECTS] s WITH (NOLOCK))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-07 : 09:38:49

Executing that code tells me "Error 11/7/2011 8:39:11 AM 0:00:00.046 SQL Server Database Error: Invalid column name 'RECEIPTS'. 5 0

"RECEIPTS" is the table from which i'm looking for mismatched entries in "Receiptno".

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-07 : 09:39:52

Disregard. Got it. Thank you.

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-07 : 09:44:40

hi you need to specify column of receipts table some thing like below in your where clause of inner query

[CIVIL].[dbo].[RECEIPTS].RECEIPTNO

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 09:46:55

quote:
Originally posted by WJHamel

Disregard. Got it. Thank you.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 09:49:25

quote:
Originally posted by jassi.singh

hi you need to specify column of receipts table some thing like below in your where clause of inner query

[CIVIL].[dbo].[RECEIPTS].RECEIPTNO

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

Huh
What does that mean? Can you show us?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources