Good Afternoon.

I have a wine database that I am trying to clean up for a migration.

My problem is that I have tens of thousands of records in a table with multiple duplicate RegionNames (hundreds of multiples for each RegionName) -

Two tables are being accessed - The Bottle table that holds all of the wine bottle information and the Region table that hold the world wide regions for the wine. The Region Table has multiple duplicate IDs for the same region (over 500 Regions) and the Bottle Table references many if not all of these duplicates RegionIDs throughout the table.

My goal was to update the Bottle table to reflect just one RegionID for each RegionName - I will specify the RegionID that I will use in the set command - (Later, i will delete all duplicates from the Region table)

My attempt below doesnt work but in the "WHERE [RegionID] =" I was trying to get only those RegionIDs both the RegionIDs from each table Match, The RegionName is specified, and to omit all the RegionIDs that are already 3125.

If anyone has any suggestions I would be extremely grateful. (I am just working on cleaning data for a migration - I do not have much SQL experience, so be gentle if there are obvious syntax or other mistakes)



UPDATE [HRCTEST].[dbo].[Bottle]
SET [RegionID] = 3125
WHERE [RegionID] = (SELECT *
FROM HRCTEST.dbo.Bottle, HRCTEST.dbo.Region
WHERE Bottle.RegionID = Region.RegionID and Region.RegionName = 'Alsace' and Bottle.RegionID <> 3125)

GO

(as a normal select statement - this returns good data, but as a subquery, i get this error below:
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "Bottle.RegionID" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "Region.RegionID" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "Region.RegionName" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "Bottle.RegionID" could not be bound.
Msg 116, Level 16, State 1, Line 5
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.



Data Information:
The Region table contains only the RegionID & RegionName (This table has hundreds of duplicate RegionNames) After I update the Bottle Table, I will be deleting the duplicates from this table to reflect only One RegionName and a corresponding RegionID

The Bottle table contains all of the bottle descriptive data including the RegionID. I am updating the records in this table to contain only 1 RegionID for a corresponding RegionName.

Thanks!

Jamie

I found the correct syntax. Thanks for looking into the problem for those that did.

Much appreciated.

The fix is below.

Jamie


UPDATE [HRCTEST].[dbo].[Bottle]
SET [RegionID] = 3125
FROM [HRCTEST].[dbo].[Bottle] INNER JOIN [HRCTEST].[dbo].[Region]
ON HRCTEST.dbo.Bottle.RegionID = HRCTEST.dbo.Region.RegionID AND HRCTEST.dbo.Region.RegionName = 'Alsace'
WHERE HRCTEST.dbo.Region.RegionID <> 3125
GO

The old 1970's Sybase syntax is unpredictable, not portable and conceptually wrong.

CREATE TABLE Orders
(order_nbr INTEGER NOT NULL PRIMARY KEY,
some_col DECIMAL (9,2) NOT NULL);

INSERT INTO Orders
VALUES (1, 0), (2, 0), (3, 0);

CREATE TABLE Order_Details
(order_nbr INTEGER NOT NULL,
sku INTEGER NOT NULL,
item_price DECIMAL (9,2) NOT NULL,
PRIMARY KEY(order_nbr, sku),
-- FOREIGN KEY(sku) REFERENCES Products(sku)
FOREIGN KEY(order_nbr) REFERENCES Orders(order_nbr));

INSERT INTO Order_Details VALUES (1, 1, 500.00);
INSERT INTO Order_Details VALUES (1, 2, 205.00);
INSERT INTO Order_Details VALUES (2, 1, 490.95);
INSERT INTO Order_Details VALUES (3, 1, 480.00);

SELECT * FROM Orders;

UPDATE Orders
SET Orders.some_col =Order_Details.item_price
FROM Orders
INNER JOIN
Order_Details
ON Orders.order_nbr =Order_Details.order_nbr;

/* results -- see item #1; last physical value
1 205.00 - where is the $500.00?
2 490.95
3 480.00
*/

--repeat with new physical ordering
DELETE FROM Order_Details;
DELETE FROM Orders;
DROP INDEX Order_Details.foobar;

-- index will change the execution plan
CREATE INDEX foobar ON Order_Details (order_nbr, item_price);

INSERT INTO Orders VALUES (1, 0);
INSERT INTO Orders VALUES (2, 0);
INSERT INTO Orders VALUES (3, 0);

INSERT INTO Order_Details VALUES (1, 2, 205.00);
INSERT INTO Order_Details VALUES (1, 1, 500.00);
INSERT INTO Order_Details VALUES (2, 1, 490.95);
INSERT INTO Order_Details VALUES (3, 1, 480.00);

UPDATE Orders
SET Orders.some_col = Order_Details.item_price
FROM Orders
INNER JOIN
Order_Details
ON Orders.order_nbr = Order_Details.order_nbr;

SELECT * FROM Orders;

/*
Results
1 500.00
2 490.95
3 480.00
*/

What is the first property that you must have in an INDEX? It cannot change the results of a statement, only the performance. See the problem?

Can you get an industry standard for the region codes?

We have the ANSI/ISO Standard MERGE statement which will catch this and other problems. Use it instead.