Help with creating an Update query

• iain-656842

  SSC Eights!

  Points: 852

  More actions

  August 13, 2010 at 11:34 pm

  #227937

  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

• Lynn Pettis

  SSC Guru

  Points: 442447

  More actions

  August 14, 2010 at 1:47 am

  #1207012

  Would help if you also provided the code you have already tried as well as expected results based on the sample data.

  By the way, it looks like you should look at normalizing your data, not to happy with the first table.