How to create an update query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to create an update query

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-08-14 : 01:28:22

Whats the best way to do the following update query.

I've been thinking about using lots of union queries and making view etc but keep getting myself in a muddle.

Heres the scenario...
I have a row in a table (the row has about 300 fields in it)
Certain fields may have an 'X' this means that the item has been selected. However the real name of the item selected is the value of the "Corresponding" field. e.g. if they put an 'X' in the field called Item01 the actual value that should be used is the value in Item01Name in the same row(see example below)
I then need to take that selected value (for example) 'Beef' and look up its French equivalent in another table.
Finally writing back to an empty field at the end of the original table with all the frech choices selected with a comma and space between each one. In reality i'mnot working with food items but i thought it might make a good example to work with.
(NOTE - the values in the fields not selected are usually filled with a dash '-', they are not NULL , i didn't know if that is important or not but thought i might be worth mentioning.
Summing up - what I need to try to get into the field FRChoice for the row in the example is
'Bouef, Moutarde, Pain, Dessert'
Hope this all makes sense

Create table F1(
recid varchar(10) null,
Item01 varchar(3) null ,
Item01Name varchar(10) null ,
Item02 varchar(3) null ,
Item02Name varchar(10) null ,
Item03 varchar(3) null ,
Item03Name varchar(10) null ,
Item04 varchar(3) null ,
Item04Name varchar(10) null ,
Item05 varchar(3) null ,
Item05Name varchar(10) null ,
Item06 varchar(3) null ,
Item06Name varchar(10) null ,
Item07 varchar(3) null ,
Item07Name varchar(10) null ,
Item08 varchar(3) null ,
Item08Name varchar(10) null ,
Item09 varchar(3) null ,
Item09Name varchar(10) null ,
FRChoice varchar(100) null ,
);

insert into F1 values ('167999','-','Tomato','X', 'Beef', '-','Potato','-','Gravy','X','Mustard', 'X','Bread','-','Butter','-','Peas','X','Trifle',null);

Create table F2(
EngName varchar(10) null ,
FrenchName varchar(10) null
);

insert into F2 values ('Tomato', 'Tomates');
insert into F2 values ('Beef', 'Bouef');
insert into F2 values ('Potato', 'PommesDT');
insert into F2 values ('Gravy', 'Jus');
insert into F2 values ('Mustard', 'Moutarde');
insert into F2 values ('Bread', 'Pain');
insert into F2 values ('Butter', 'Beurre');
insert into F2 values ('Peas', 'Petit pois');
insert into F2 values ('Trifle', 'Dessert');

select * from F1
Select * from F2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 02:03:24

here you go


Create table #F1(
recid varchar(10) null, 
Item01 varchar(3) null ,
Item01Name varchar(10) null ,
Item02 varchar(3) null ,
Item02Name varchar(10) null ,
Item03 varchar(3) null ,
Item03Name varchar(10) null ,
Item04 varchar(3) null ,
Item04Name varchar(10) null ,
Item05 varchar(3) null ,
Item05Name varchar(10) null ,
Item06 varchar(3) null ,
Item06Name varchar(10) null ,
Item07 varchar(3) null ,
Item07Name varchar(10) null ,
Item08 varchar(3) null ,
Item08Name varchar(10) null ,
Item09 varchar(3) null ,
Item09Name varchar(10) null ,
FRChoice varchar(100) null ,
);

insert into #F1 values ('167999','-','Tomato','X', 'Beef', '-','Potato','-','Gravy','X','Mustard', 'X','Bread','-','Butter','-','Peas','X','Trifle',null);

Create table #F2(
EngName varchar(10) null ,
FrenchName varchar(10) null 
);

insert into #F2 values ('Tomato', 'Tomates');
insert into #F2 values ('Beef', 'Bouef');
insert into #F2 values ('Potato', 'PommesDT');
insert into #F2 values ('Gravy', 'Jus');
insert into #F2 values ('Mustard', 'Moutarde');
insert into #F2 values ('Bread', 'Pain');
insert into #F2 values ('Butter', 'Beurre');
insert into #F2 values ('Peas', 'Petit pois');
insert into #F2 values ('Trifle', 'Dessert');

--select * from F1
--Select * from F2
;With CTE
AS
(

SELECT recid,u.ItemVal,u.ItemCat
FROM 
(
SELECT 
recid,
CAST(Item01 AS varchar(10)) AS Item01,
CAST(Item02 AS varchar(10)) AS Item02,
CAST(Item03 AS varchar(10)) AS Item03,
CAST(Item04 AS varchar(10)) AS Item04,
CAST(Item05 AS varchar(10)) AS Item05,
CAST(Item06 AS varchar(10)) AS Item06,
CAST(Item07 AS varchar(10)) AS Item07,
CAST(Item08 AS varchar(10)) AS Item08,
CAST(Item09 AS varchar(10)) AS Item09,
Item01Name,
Item02Name,
Item03Name,
Item04Name,
Item05Name,
Item06Name,
Item07Name,
Item08Name,
Item09Name
FROM #F1
)
m
UNPIVOT (ItemVal FOR ItemCat IN (Item01,
Item01Name,
Item02,
Item02Name,
Item03,
Item03Name,
Item04,
Item04Name,
Item05,
Item05Name,
Item06,
Item06Name,
Item07,
Item07Name,
Item08,
Item08Name,
Item09,
Item09Name))u 
)


UPDATE f1
SET f1.FRChoice=LEFT(ftmp.lst,LEN(ftmp.lst)-1)
FROM #F1 f1
CROSS APPLY
(
SELECT c2.FrenchName + ','
FROM CTE c1
CROSS APPLY (SELECT f2.FrenchName 
             FROM CTE c
			 INNER JOIN #F2 f2
			 ON f2.EngName =   c.ItemVal         
             WHERE ItemCat = c1.ItemCat + 'Name'
            )c2  
WHERE c1.ItemVal='X'
AND c1.recid=f1.recid
 FOR XML PATH('')
)ftmp(lst)

select * from #F1

drop table #F1
drop table #F2


output
-----------------------------------------------
recid      Item01 Item01Name Item02 Item02Name Item03 Item03Name Item04 Item04Name Item05 Item05Name Item06 Item06Name Item07 Item07Name Item08 Item08Name Item09 Item09Name FRChoice
---------- ------ ---------- ------ ---------- ------ ---------- ------ ---------- ------ ---------- ------ ---------- ------ ---------- ------ ---------- ------ ---------- -------------------------------------
167999     -      Tomato     X      Beef       -      Potato     -      Gravy      X      Mustard    X      Bread      -      Butter     -      Peas       X      Trifle     Bouef,Moutarde,Pain,Dessert