locked
Updating one SQL table based on results of another... RRS feed

  • Question

  • Hi,

    I will start by saying I am fairly new to SQL (less than 24 hours).

    I have 2 tables, InventoryItem and InventoryItemWebOption. I need to update a column in InventoryItemWebOption called 'published' based on a result in both tables. The value will need to be set to '1' based on InventoryItem.ManufacturerCode = 'X' and InventoryItemWebOption.WebSiteCode = 'Y'.

    Can anyone point me in the right direction to get me started?

    Many thanks,

    Jamie @ Fuzion

    Thursday, April 14, 2011 11:32 AM

Answers

  • Hi Jamie_Fuzion,

    You should confirm the relationship between InventoryItem and InventoryItemWebOption tables first. Does there have a Foreign Key Constraints? Try to post the DDL statements of your table structures then we can make a better investigation.

    Best Regards,
    Stephanie Lv

    Friday, April 15, 2011 11:12 AM
  • Jamie,

    I agree with Stephanie in it would be much better that you will post your DDL.

    try following:

    Update InventoryItemWebOption 
    Set Published = 1 
    From 
      InventoryItemWebOption 
      Inner Join InventoryItem On InventoryItemWebOption.InventoryItem = InventoryItem.Inventory 
    Where InventoryItem.ManufacturerCode = 'X' and InventoryItemWebOption.WebSiteCode = 'Y'
    
    
    

    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Friday, April 15, 2011 4:18 PM

All replies

  • I suggest you read up on nested subqueries.

    Here's an article to get you started:

    http://msdn.microsoft.com/en-us/library/ms189575(v=SQL.100).aspx

     

    Thursday, April 14, 2011 1:16 PM
  • Thank you for this. I guess this may be a little too advanced for me at the moment!

    I am fairly new to SQL and have grasped a little, just didnt expect this to be so difficult.

    Thanks again,

    Jamie

    Thursday, April 14, 2011 2:35 PM
  • Update InventoryItemWebOption 
    Set Published = 1 
    From 
        InventoryItemWebOption 
        Inner Join InventoryItem On InventoryItemWebOption.PK = InventoryItem.PK And InventoryItem.ManufacturerCode = 'X' and InventoryItemWebOption.WebSiteCode = 'Y'
    

    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Thursday, April 14, 2011 4:32 PM
  • Hi Arbi,

    Thank you for this. When I try and verify this it comes back with Invalid column name 'PK' twice.

    Any ideas?

    Jamie

    Thursday, April 14, 2011 4:47 PM
  • PK = Primary Key. I do not know what are your primary keys.
    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Thursday, April 14, 2011 4:49 PM
  • How is your relation between InventoryItemWebOption And InventoryItem ? Do you have any ID like InventoryId, InventoryItemId, ...????
    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Thursday, April 14, 2011 4:51 PM
  • Hi Arbi,

    We have a column called ItemCode in both tables which relate the items together. I have placed that where you had the PK in the statement, however it seems to change every product to published and not the crieria we have asked for.

    Any ideas?

    Thanks,

    Jamie

    Friday, April 15, 2011 7:28 AM
  • Hi Jamie_Fuzion,

    You should confirm the relationship between InventoryItem and InventoryItemWebOption tables first. Does there have a Foreign Key Constraints? Try to post the DDL statements of your table structures then we can make a better investigation.

    Best Regards,
    Stephanie Lv

    Friday, April 15, 2011 11:12 AM
  • Jamie,

    I agree with Stephanie in it would be much better that you will post your DDL.

    try following:

    Update InventoryItemWebOption 
    Set Published = 1 
    From 
      InventoryItemWebOption 
      Inner Join InventoryItem On InventoryItemWebOption.InventoryItem = InventoryItem.Inventory 
    Where InventoryItem.ManufacturerCode = 'X' and InventoryItemWebOption.WebSiteCode = 'Y'
    
    
    

    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    Friday, April 15, 2011 4:18 PM