Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2012
    Posts
    4

    Update with Conditional Where

    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
    Last edited by Sportzter; 12-11-12 at 20:09.

  2. #2
    Join Date
    Oct 2012
    Posts
    4
    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

  3. #3
    Join Date
    Jan 2013
    Posts
    306
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •