mysql query

[bugcoder]

I have three columns table tbl_foods with sample data as follows

 

id food_id weight

1  1050 cup, tbsp

2  1050 oz

3  1053 tbsp

4  1058 cup

5  1058 cup,oz

6  1069 oz

7  1070 tbsp

8  1071 fluid

9  1078 tbsp

 

 

I want every food_id which does not have "oz" as weight should be inserted with new id in this table

e.g now 1053,1070,1071 and 1078 will be inserted with new ids and wights as "oz".

[myrddinwylt]

For this table assuming it's name is "food"

 

id food_id weight
1   1050   cup, tbsp
2   1050   oz
3   1053   tbsp
4   1058   cup
5   1058   cup,oz
6   1069   oz
7   1070   tbsp
8   1071   fluid
9   1078   tbsp

 

Try

 

SELECT * FROM `food` WHERE `weight` NOT LIKE '%oz%';

[bugcoder]

Thanks for reply

 

actually i want to insert new ids against those distinct food_item_id returned by this query

[myrddinwylt]

Assuming you are going to insert back into the same table, and the field "ID" is set as Primary Index, AutoIncrement, you can do the following

 

INSERT INTO 'food' (SELECT null as id, food_id, 'oz' as weight FROM `food` WHERE `weight` NOT LIKE '%oz%');

 

Or if you are wanting to modify those records, and not insert new ones, then

 

UPDATE food SET weight = 'oz' WHERE `weight` NOT LIKE '%oz%');