September 30, 2008 at 9:24 am
Good Morning,
I am trying to update a ship ID table from data in another table. I have created a update query but it tries to update all the shipid fields in the the orderdetails table to the max(ShipID) but what I really want it to do is update the orderdetails.shipID from whatever is inputted in the shipping.shipid table but only update where the shipping.eeid = orders.EEID, so in theory there should only be one update. I am not sure what it is I am doing wrong here, I appreciate any help, thank you
Here is the query:
UPDATE OrderDetails
SET ShipID = (Select MAX(Shipping.ShipID)
FROM Shipping INNER JOIN
Orders ON Shipping.EEID = Orders.EEID )
from OrderDetails
September 30, 2008 at 9:30 am
Try something like this:
UPDATE OrderDetails
SET ShipID = LatestShipping
from OrderDetails INNER JOIN
(Select EEID , MAX(Shipping.ShipID) AS LatestShipping
FROM Shipping ) sub
ON Sub.EEID= Orders.EEID
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2008 at 9:39 am
I tried it and got the below error:
The column prefix 'Orders' does not match with a table name or alias name used in the query.
September 30, 2008 at 9:49 am
What's the join between orders and orderdetails?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2008 at 9:53 am
I am trying to join Orders.EEID to Shipping.EEID
September 30, 2008 at 10:07 am
Except that you're not including the ORDERS table in your FROM clause. If you do include it there, you need to join it to your shipping table AND the orderdetails table. Otherwise - you need to find some way to join the orderdetails table directly to shipping.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 30, 2008 at 10:21 am
vanessachase81 (9/30/2008)
I am trying to join Orders.EEID to Shipping.EEID
Yes, but how does that relate to the row that you're trying to update in OrderDetails?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2008 at 10:35 am
I think that is where my problem lies. See in order to update the orderdetails.shipid to what is in the shipping.shipid, I have to somehow join the orders table in order to retrieved the EEID and compare that to the shipping.EEID.
So for example:
I have Shipping EEID- E5463
Shipping ShipID-38339
OrderDetail ShipID- null
Orders EEID- E5463
I want to update the OrderDetail ShipID = Shipping ShipID
Where the Shipping.EEID= Orders.EEID
The things common is the Orders.DetailID = OrderDetail.ID so maybe I can join something like:
OrderDetails INNER JOIN
Orders ON OrderDetails.OrderID = Orders.OrderID INNER JOIN
Shipping ON OrderDetails.ShipID = Shipping.ShipID
September 30, 2008 at 10:47 am
Yup, so something like this should work
UPDATE OrderDetails
SET ShipID = LatestShipping
from OrderDetails
INNER JOIN Orders on Orders.DetailID = OrderDetail.ID
INNER JOIN
(Select EEID , MAX(Shipping.ShipID) AS LatestShipping
FROM Shipping ) sub
ON Sub.EEID= Orders.EEID
Hmm, you have a single order detail per order? That's an odd design.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2008 at 12:57 pm
How to I fix this error:
Column 'Shipping.EEID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
UPDATE OrderDetails
SET ShipID = LatestShipping
from OrderDetails
INNER JOIN Orders on Orders.OrderID = OrderDetails.OrderID
INNER JOIN (Select EEID , MAX(Shipping.ShipID) AS LatestShipping
FROM Shipping ) sub
ON Sub.EEID= Orders.EEID
September 30, 2008 at 1:08 pm
The sub-query needs a GROUP BY clause. As in -
UPDATE OrderDetails
SET
ShipID = LatestShipping
from OrderDetails
INNER JOIN Orders on Orders.OrderID = OrderDetails.OrderID
INNER JOIN (
Select EEID ,
MAX(Shipping.ShipID) AS LatestShipping
FROM Shipping
GROUP BY EEID
) sub
ON Sub.EEID= Orders.EEID
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 30, 2008 at 1:20 pm
Sorry. 🙁
That's what I get for posting while I'm doing something else.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2008 at 9:32 am
Okay, I have the MS SQL query how do I convert this to access?
UPDATE OrderDetails
SET ShipID = LatestShipping
from OrderDetails
INNER JOIN Orders on Orders.OrderID = OrderDetails.OrderID
INNER JOIN (Select shipping.EEID ,MAX(Shipping.ShipID) AS LatestShipping
FROM Shipping GROUP BY EEID ) sub
ON Sub.EEID= Orders.EEID
Where OrderDetails.ShipID is null AND OrderDetails.Tracking is null AND OrderDetails.ShipDate is null
October 2, 2008 at 10:31 am
You might want to be a bit more specific about exactly what you want to convert to Access, but that query could be used un-changed as a pass-through query in Access. I'm not familiar with how to do that in versions of Access prior to 2007, but I do know it's at least possible in Access 2003.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply