Update Bad With Good

[iamtrying]

Hello I have the query below in SQL Server 2005

UPDATE InMwhBad INNER JOIN InMwhGood ON InMwhBad.[Item #] = InMwhGood.[Item #] SET InMwhBad.[Bin Location] = [InMwhGood].[Bin Location]
WHERE (("where warehouse"="RA"));

I am trying update the records in the InMwhBad table with the records in the InMwhGood table with the same Item# and warehouse location = RA

When I run the above query I get
Error Source:.Net SqlClient Data Provider
Error Source: Incorrect syntax near the keyword 'INNER'

Stumped

 

[gmmastros]

Your syntax is just a little off. Try this:

UPDATE InMwhBad 
SET    InMwhBad.[Bin Location] = [InMwhGood].[Bin Location]
From   InMwhBad 
       INNER JOIN InMwhGood 
         ON InMwhBad.[Item #] = InMwhGood.[Item #] 
WHERE  warehouse='RA'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

Your code looks like MS Access which can not always be used as T-SQL for an update statement.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[iamtrying]

Yes it is Access code and I get an Invlaid Obect Name "InMwhBad" is SQL server. How can I make this work in SQL Server 2005

 

[iamtrying]

Here is the outcome

UPDATE InMwhBad
SET InMwhBad.[Bin Location]=[InMwhGood].[Bin Location]
From InMwhBad
INNER JOIN InMwhGood
ON InMwhBad.[Item #]=InMwhGood.[Item #]
WHERE 'InMwhBad.Warehouse'='RA'

(0 rows) affected

 

[gmmastros]

This is a problem:

WHERE 'InMwhBad.Warehouse'='RA'

You see, in SQL Server, to delimit a string, you use single quotes. That where clause would ALWAYS cause the query to not update anything. In fact, it's the same as saying.

Where 1 = 0

You need to remove the single quotes from the left side.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[iamtrying]

The query ran but the results were not what I expected. The updates did not come from the RA warehouse from the "InMwhGood" to the "InMwhBad" they came from "CO" warehouse. How can that be?

 

[markros]

Are you sure you ran this statement in the right database?

One more tip - before attempting to do an UPDATE, always try with the SELECT first.

PluralSight Learning Library