sql server: how to mass replace fields with result of a query

Heres the situation, a database was created and not normalized. The table LUT_ProductInfo contains a field called flavor that is populated with an actual flavor name (ie Coke Classic). I created a lookup table LUT_Flavors with all of the flavor names complete with PKs. I need to replace all of the flavor names in LUT_Product info with the corresponding foreign key from the LUT_Flavors table. At the moment, the flavor names in each table are identical so the swap should b开发者_开发问答e clean.

I tried something like this:

update LUT_ProductInfo 
set flavor =
     (select LUT_Flavor.id, LUT_Flavor.flavor 
      from LUT_ProductInfo prod 
      join LUT_Flavor on LUT_Flavor.flavor = prod.flavor).ID
where 
      LUT_ProductInfo.flavor = (select LUT_Flavor.id,LUT_Flavor.flavor 
                                from LUT_ProductInfo prod 
                                join LUT_Flavor
                                on LUT_Flavor.flavor = prod.flavor).flavor

But that broke some rules I guess and didn't do anything. Does anyone have any insight?

Sorry, am on the road, so no database to try this on - but try:

update LUT_ProductInfo 
set flavor = f.id
from  LUT_ProductInfo p, 
LUT_Flavour f
where f.flavor = p.flavor

Hi, What about this:



  UPDATE LUT_ProductInfo product
  SET flavor = 
  (
      select LUT_Flavor.id
      from LUT_Flavor prod 
      where prod .flavor  = product.flavor
  )

Here's an example how I would do it. Note that I add a flavourid column to the LUT_ProductInfo table. This is just to ensure that the transfer went smoothly, you can delete/rename it later or just go straight into the column if you're brave. You might want to back up your table before updating every record though, you wouldn't want to lose anything.

Anyway, here's my test code:

CREATE TABLE #LUT_ProductInfo (
    ProdID int PRIMARY KEY,
    flavor varchar(10),
    flavorid int NULL)

INSERT INTO #LUT_ProductInfo VALUES (1,'Vanilla', NULL)
INSERT INTO #LUT_ProductInfo VALUES (2,'Chocolate', NULL)
INSERT INTO #LUT_ProductInfo VALUES (3,'Strawberry', NULL)

CREATE TABLE #LUT_Flavor (
    flavorid int PRIMARY KEY,
    flavor varchar(10))

INSERT INTO #LUT_Flavor VALUES (1,'Chocolate')
INSERT INTO #LUT_Flavor VALUES (2,'Vanilla')
INSERT INTO #LUT_Flavor VALUES (3,'Strawberry')

SELECT * FROM #LUT_ProductInfo

/* What you want is here */
UPDATE prodinfo
SET flavorid = fl.flavorid
FROM #LUT_ProductInfo AS prodinfo
INNER JOIN #LUT_Flavor AS fl
ON prodinfo.flavor = fl.flavor
/* End what you want */

SELECT * FROM #LUT_ProductInfo

DROP TABLE #LUT_ProductInfo
DROP TABLE #LUT_Flavor

Hard to tell without the error message.

Maybe try: (select LUT_Flavor.id from LUT_ProductInfo prod join LUT_Flavor on LUT_Flavor.flavor = prod.flavor)

instead of

(select LUT_Flavor.id,LUT_Flavor.flavor from LUT_ProductInfo prod join LUT_Flavor on LUT_Flavor.flavor = prod.flavor).ID

If it doesn't help please paste error message.

A better way to do this is:

update LUT_ProductInfo
set flavor = fl.id
from LUT_ProductInfo pi
inner join LUT_Flavor fl on fl.flavor = pi.flavor

having a from clause and joining that way. Also this solution presumes that the data types between flavor and ID are compatible, if id is an int and the flavor is nvarchar(x) you might not get the results you want. You'll probably have to do the update, then change the data type.