Update Left Outer 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

SQL Server 2000 Forums

Transact-SQL (2000)

Update Left Outer Join

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-14 : 11:21:28

Having a little trouble with this sql...

UPDATE tblCalibrationReview SET poNumber = CalibrationReview.[PONumber],
calCertNumber = CalibrationReview.[CalCertNumber],
q1Review = (CASE CalibrationReview.ReviewQ1 WHEN 'Yes' THEN 1
WHEN 'No' THEN 0 WHEN 'N/A' THEN 3 ELSE 3 END),
followUp = CalibrationReview.[Followup]
FROM CalibrationReview
LEFT OUTER JOIN tblEquipments ON tblCalibrationReview.equipmentId = tblEquipments.ID WHERE CalibrationReview.[EquipmentNumber] = tblEquipments.AssignedID

The left outer join. I need to read CalibrationReview.EquipmentNumber, look at tblEquipments and get the ID field where that number matches and update tblCalibrationReview.equipmentId with that number.

I'm getting the error when running:

The column prefix 'tblCalibrationReview' does not match with a table name or alias name used in the query.

Thanks for any input,

Zath

Kristen
Test

22859 Posts

Posted - 2007-03-14 : 11:27:28

You haven't defined the ALIAS for tblCalibrationReview (highlighted in Red below) - you probably want to add the stuff in Blue below to create an Alias


UPDATE tblCalibrationReview
SET poNumber = tblCalibrationReview.[PONumber],
calCertNumber = tblCalibrationReview.[CalCertNumber],
q1Review = (CASE tblCalibrationReview.ReviewQ1 WHEN 'Yes' THEN 1 
WHEN 'No' THEN 0 WHEN 'N/A' THEN 3 ELSE 3 END), 
followUp = tblCalibrationReview.[Followup]
FROM CalibrationReview AS tblCalibrationReview
LEFT OUTER JOIN tblEquipments ON tblCalibrationReview.equipmentId = tblEquipments.ID 
WHERE tblCalibrationReview.[EquipmentNumber] = tblEquipments.AssignedID

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-14 : 11:32:48

What's the point in updating a table's column with itself?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-14 : 12:34:20

Thanks for the responses.

Now I'm getting this error:

The column prefix 'CalibrationReview' does not match with a table name or alias name used in the query

Here's the updated sql:

UPDATE tblCalibrationReview SET poNumber = CalibrationReview.[PONumber],
calCertNumber = CalibrationReview.[CalCertNumber],
q1Review = (CASE CalibrationReview.ReviewQ1 WHEN 'Yes' THEN 1
WHEN 'No' THEN 0 WHEN 'N/A' THEN 3 ELSE 3 END),
followUp = CalibrationReview.[Followup]
FROM CalibrationReview as tblCalibrationReview
LEFT OUTER JOIN tblEquipments ON tblCalibrationReview.equipmentId = tblEquipments.ID WHERE CalibrationReview.[EquipmentNumber] = tblEquipments.AssignedID

Basically, in the same database, I'm pulling info from one table (CalibrationReview) and inserting that info into tblCalibrationReview.

But in CalibrationReview table, there is the equipmentId which is found in tblEquipments.
So, for that one field, read CalibrationReview.[EquipmentNumber], match it in tblEquipments.AssignedID, read the coresponding tblEquipments.ID and update tblCalibrationReview.equipmentId with that number.

Any more input is appreciated.

Zath

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-14 : 12:38:54

And what is the link between CalibrationReview and tblCalibrationReview table?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-14 : 12:46:24

Link?

I don't follow you. I'm just taking the info from that table and putting it in another table and adjusting it.

Then I'll delete the CalibrationReview table.

I imported it from access and am integrating a small system to the large information management system I'm developing.

Zath

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-14 : 13:33:40

Ok, made some adjustments and get no errors.

But 0 rows effected!

Here's the latest:

UPDATE [dbo].[tblCalibrationReview] SET [dbo].[tblCalibrationReview].[poNumber] = a.[PONumber],
[dbo].[tblCalibrationReview].[calCertNumber] = a.[CalCertNumber],
[dbo].[tblCalibrationReview].[q1Review] = (CASE a.[ReviewQ1] WHEN 'Yes' THEN 1
WHEN 'No' THEN 0 WHEN 'N/A' THEN 3 ELSE 3 END),
[dbo].[tblCalibrationReview].[followUp] = a.[Followup]
FROM [dbo].[CalibrationReview] as a, [dbo].[tblCalibrationReview] as b
LEFT OUTER JOIN [dbo].[tblEquipments] ON b.[equipmentId] = [dbo].[tblEquipments].[ID] WHERE a.[EquipmentNumber] = [dbo].[tblEquipments].[AssignedID]

Zath

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-14 : 13:51:15

quote:

LEFT OUTER JOIN [dbo].[tblEquipments] ON b.[equipmentId] = [dbo].[tblEquipments].[ID] WHERE a.[EquipmentNumber] = [dbo].[tblEquipments].[AssignedID]

That doesn't make any sense .... what is that WHERE clause there for? It looks like that should be part of the join condition:

LEFT OUTER JOIN [dbo].[tblEquipments] ON b.[equipmentId] = [dbo].[tblEquipments].[ID] ~~WHERE~~ AND a.[EquipmentNumber] = [dbo].[tblEquipments].[AssignedID]

- Jeff
http://weblogs.sqlteam.com/JeffS

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-14 : 14:01:47

I changed the last WHERE to AND and get this error:

The column prefix 'a' does not match with a table name or alias name used in the query.

Zath

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-14 : 14:09:38

I even changed it around to this and get 0 Rows effected:

UPDATE [dbo].[tblCalibrationReview] SET [dbo].[tblCalibrationReview].[poNumber] = a.[PONumber],
[dbo].[tblCalibrationReview].[calCertNumber] = a.[CalCertNumber],
[dbo].[tblCalibrationReview].[q1Review] = (CASE a.[ReviewQ1] WHEN 'Yes' THEN 1
WHEN 'No' THEN 0 WHEN 'N/A' THEN 3 ELSE 3 END),
[dbo].[tblCalibrationReview].[followUp] = a.[Followup],
[dbo].[tblCalibrationReview].[equipmentId] = (SELECT ID FROM tblEquipments Where a.[EquipmentNumber] = [dbo].[tblEquipments].[AssignedID])
FROM [dbo].[CalibrationReview] as a

Zath

Kristen
Test

22859 Posts

Posted - 2007-03-14 : 15:52:51

Isn't


UPDATE [dbo].[tblCalibrationReview]
SET ...
FROM [dbo].[CalibrationReview] as a

going to make a Cartesian join between [dbo].[tblCalibrationReview] and [dbo].[CalibrationReview] (now aliased as "a")?

Kristen

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-14 : 17:28:58

Don't know.

All I need to do is update a table from 2 different tables.

Guess I'll do it with code behind.

Zath

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 17:43:02

Maybe you want to post some sample data for the three tables and also show us the expected result?

Peter Larsson
Helsingborg, Sweden

JohnH
Starting Member

13 Posts

Posted - 2007-03-14 : 18:03:03

I'd suggest you start here.

UPDATE dbo.tblCalibrationReview
SET
poNumber = A.PONumber,
calCertNumber = A.CalCertNumber,
q1Review = (CASE A.ReviewQ1 WHEN 'Yes' THEN 1
WHEN 'No' THEN 0 ELSE 3 END),
followUp = A.Followup,
equipmentId = E.[ID]
FROM
dbo.CalibrationReview as A
LEFT JOIN tblEquipments E
ON A.EquipmentNumber = E.AssignedID

I've aliased both your source tables and re-formatted your SQL to make it easier on myself. Take a close look at the LEFT JOIN - I'm guessing at your intent here.

Speaking as one who's been there, you'll eventually regret it if you do this job with code behind.
If the SELECT above gives you the desired results, try this for the UPDATE:

SELECT
A.PONumber,
A.CalCertNumber,
CASE A.ReviewQ1
WHEN 'Yes' THEN 1
WHEN 'No' THEN 0
ELSE 3
END AS 'q1Review',
A.Followup,
A.EquipmentNumber,
E.AssignedID,
E.[ID]
FROM
dbo.CalibrationReview as A
LEFT JOIN tblEquipments E
ON A.EquipmentNumber = E.AssignedID

John Hopkins

JohnH
Starting Member

13 Posts

Posted - 2007-03-14 : 18:04:05

Eventually I'll learn how to get keep the forum from re-formatting my re-formatted stuff ...

John Hopkins

Kristen
Test

22859 Posts

Posted - 2007-03-14 : 22:27:45

JohnH - your UPDATE is going to do a Cartesian join, isn't it?

I think the O/P is after something like:


UPDATE U
SET poNumber = C.[PONumber],
    calCertNumber = C.[CalCertNumber],
    q1Review = CASE C.ReviewQ1 
                   WHEN 'Yes' THEN 1 
                   WHEN 'No' THEN 0 
                   WHEN 'N/A' THEN 3 
                   ELSE 3
                   END, 
     followUp = C.[Followup]
FROM tblCalibrationReview AS U
     JOIN CalibrationReview AS C
         ON C.MyPK = U.MyPK
     LEFT OUTER JOIN tblEquipments AS E
          ON C.equipmentId = E.ID 
         AND C.[EquipmentNumber] = E.AssignedID

tblEquipments doesn;t seem to be used, so I've struck it out

Kristen

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2007-03-15 : 08:28:57

JohnH, the select worked as expected and it showed something I didn't notice.

Here's a partial of tblCalibrationReview:

CREATE TABLE [dbo].[tblCalibrationReview] (
id int IDENTITY(1,1) CONSTRAINT tblCalibrationReview_PK PRIMARY KEY NOT NULL ,
equipmentId int NOT NULL,
CONSTRAINT FK_ID_tblEquipmentRepair FOREIGN KEY (equipmentId)
REFERENCES tblEquipments (ID),

When the select ran, some equipmentId's were NULL. It won't update due to the contraint.

So, what I need to do first is:

Loop through CalibrationReview table and check tblEquipments to see if the EquipmentNumber exists.

If not, insert this into tblEquipments getting the following fields:
EquipmentNumber, Model, SerialNumber, Make, CalDate and CalLabName.

But the Make and CalLabName in CalibrationReview is spelled out, not the number assigned to it. So, need to read tblManufacturers and tblLabs and get this number to input into tblEquipments.

And when a row is added to tblEquipments, give Status field a '1'

This one is almost the same as the one I'm working on.

So, I'll see how far I can get with that one, but if someone has a quick generic sample....

Thanks,

Zath

shijobaby
Starting Member

44 Posts

Posted - 2009-08-20 : 03:03:32

the column prefix does not match with a table name or alias name used in the query.

I was also working to solve this error

Nw i found why it is comming just needs to make small changes in query

http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-messages-msg-107.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources