August 16, 2005 at 1:50 pm
Need SQL to accomplish the following:
TBL_A Contents:
ID OrderID InstanceID GlobalProductID
349408 415477 142976 201
360458 415477 142976 31
555555 777888 765432 201
666888 777888 765432 31
TBL_B Contents:
ID OrderItemID
11067 349408
12345 555555
I need to:
UPDATE TBL_B SET OrderItemID = TBL_A ID
WHERE TBL_B OrderItemID is in TBL_A and it's OrderID list
AND TBL_A GlobalProductID <> (201)
The only common data relationship I have
between the 2 tables is:
TBL_A ID = 349408
TBL_B OrderItemID = 349408
My final Result on TBL_B would look like this:
TBL_B Contents:
ID OrderItemID
11067 360458
12345 666888
August 16, 2005 at 2:07 pm
How do you relate '12345' ID in Table B with '666888' ID in Table A
Regards,
gova
August 16, 2005 at 2:26 pm
if like u said
"""The only common data relationship I have
between the 2 tables is:
TBL_A ID = 349408
TBL_B OrderItemID = 349408 """
then u cannot update either of the 2 tables by joining them; u need first to have them linked by keys.
Sergei
August 17, 2005 at 1:59 am
Hi,
I was wondering if what you are trying to achieve is to replace the GlobalProductID '201' items in TBL_B with non '201' items, using the fact that the OrderID is the link between the '201' and non '201' records.
What you need to do if this is the case is a self-join between two instances of TBL_A, one containing the '201' items and the other containing the non-'201' items you want to replace them with.
This will only work if there is a single OrderItemID record returned by the sub-select, in other words, if there is only 1 item with a GlobalProductID of 31 for each item with a GlobalProductID of 201 in each order.
I have used '=31' in the code below for precision, but with the limited data set shown you could use '<>201'
create table TBL_A
(ID integer, OrderID integer, InstanceID integer, GlobalProductID integer)
insert into TBL_A (ID,OrderID, InstanceID, GlobalProductID)
SELECT 349408,415477,142976,201
UNION SELECT 360458,415477,142976,31
UNION SELECT 555555,777888,765432,201
UNION SELECT 666888,777888,765432,31
SELECT * FROM TBL_A
CREATE TABLE TBL_B (ID integer, OrderItemID integer)
INSERT INTO TBL_B (ID, OrderItemID)
SELECT 11067, 349408
UNION SELECT 12345, 555555
SELECT * FROM TBL_B
--11067 349408
--12345 555555
UPDATE TBL_B
SET OrderItemID =
(SELECT A2.ID
FROM TBL_A A1
JOIN TBL_A A2
ON (A2.OrderID=A1.OrderID AND A2.GlobalProductID=31)
WHERE A1.ID = TBL_B.OrderItemID
AND A1.GlobalProductID=201)
SELECT * FROM TBL_B
--11067 360458
--12345 666888
David
If it ain't broke, don't fix it...
August 17, 2005 at 8:25 am
I don't think that would work david. Let us try to add few more rows in TBL_A and TBL_B. It would be impossible unless there is a definite way to link the tables.
SET NOCOUNT ON
create table TBL_A
(ID integer, OrderID integer, InstanceID integer, GlobalProductID integer)
insert into TBL_A (ID,OrderID, InstanceID, GlobalProductID)
SELECT 349408,415477,142976,201
UNION SELECT 360458,415477,142976,31
UNION SELECT 555555,777888,765432,201
UNION SELECT 666888,777888,765432,31
UNION SELECT 777777,777888,765433,201
UNION SELECT 888999,888999,765433,31
SELECT * FROM TBL_A
CREATE TABLE TBL_B (ID integer, OrderItemID integer)
INSERT INTO TBL_B (ID, OrderItemID)
SELECT 11067, 349408
UNION SELECT 12345, 555555
UNION SELECT 56789, 888888
SELECT * FROM TBL_B
--11067 349408
--12345 555555
UPDATE TBL_B
SET OrderItemID =
(SELECT A2.ID
FROM TBL_A A1
JOIN TBL_A A2
ON (A2.OrderID=A1.OrderID AND A2.GlobalProductID=31)
WHERE A1.ID = TBL_B.OrderItemID
AND A1.GlobalProductID=201)
SELECT * FROM TBL_B
DROP TABLE TBL_B, TBL_A
Regards,
gova
August 17, 2005 at 8:30 am
I wound up using CURSOR processing to accomplish this:
--Declare/Open/Fetch/Close/Deallocate CURSOR sample
declare @OrderID int, @ResetID1 int, @ResetID2 int, @OrderItemID int
declare OrderItemTaxCursor cursor for
select OrderItemID from EC_OrderItemTaxes order by OrderItemID
open OrderItemTaxCursor
fetch next from OrderItemTaxCursor into @OrderItemID --478778
while @@fetch_status = 0
begin
select @ResetID1 =
(select min(OrderID) from EC_OrderItems where ID = @OrderItemID)
select @ResetID2 =
(select min(ID) from EC_OrderItems where OrderID = @ResetID1 and GlobalProductID not in (201,202,203))
update EC_OrderItemTaxes set OrderItemID = @ResetID2 where current of OrderItemTaxCursor
-- get next OrderItem row
fetch next from OrderItemTaxCursor into @OrderItemID
end
-- Clean up cursor
close OrderItemTaxCursor
deallocate OrderItemTaxCursor
August 17, 2005 at 8:35 am
Do you mind posting the schema and sample data. There must be a way to do this without a cursor.
Regards,
gova
August 17, 2005 at 8:37 am
GOVIN - the schema and sample data are listed as the first entry (at top of this page)
August 17, 2005 at 10:20 am
Try this no cursor or loop
UPDATE EC_OIT
SET
OrderItemID = EC_M.OrderID
FROM
EC_OrderItemTaxes EC_OIT
JOIN
(
SELECT Match.[ID] TaxID, EC_OI.[ID] OrderID
FROM
EC_OrderItems EC_OI
LEFT JOIN
(
SELECT IT.[ID], OI.OrderID
FROM
EC_OrderItemTaxes IT
JOIN
EC_OrderItems OI
ON
IT.OrderItemID = OI.[ID]) Match
ON
EC_OI.OrderID = Match.OrderID
WHERE
EC_OI.GlobalProductID <> 201) EC_M
ON
EC_OIT.[ID] = EC_M.[TaxID]
Regards,
gova
August 17, 2005 at 1:57 pm
I think that this can be done with a simpler, single query, thusly:
Update TBL_B
Set OrderItemID =
Coalesce((Select Max(ID) maxID
FROM TBL_A A2
INNER JOIN
(Select OrderID, InstanceID
FROM TBL_A A3
WHERE ID = TBL_B.OrderItemID) X
on A2.OrderID = X.OrderID
AND A2.InstanceID = X.InstanceID
AND A2.ID TBL_B.OrderItemID
WHERE GlobalProductID not in (201,202,203))
, OrderItemID)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply