How can I update Crate IDs of List of Fruits in single SQL query in c#

Question

In reference to my question,

how can i update SQL table logic

I want a query which will do something like this,

enter image description here

I my last question was confusing hence I am asking a different question.

How can I update Crate IDs of List of Fruits in single SQL query in c#

FruitID and CrateID are foriegn keys and will always in other tables.

score 3 · Accepted Answer · answered May 14 '13 at 09:47

3

Try using IN:

UPDATE FRUITS
SET CRATE = 'CRATE 7'
WHERE FRUITID IN (1, 2, 3)

Or

UPDATE FRUITS
SET CRATE = 'CRATE 7'
WHERE FRUITName IN ('Mango1', 'Mango2', 'Apple 6')

You can also use LIKE to match Mango and Orange however this may update the incorrect results (Say you want Orange 199 to be updated to Crate 7 but want Orange 198 to be Crate 6)

answered May 14 '13 at 09:47

Darren

68,902
24
138
144

+1, Thanks, thats more like what I was looking for, but what if table doesn't have FruitID in it sir – Mathematics May 14 '13 at 09:49
@user13814 - can you post your schema? – Darren May 14 '13 at 09:49
I have added table structure in earlier question sir, please check, thanks – Mathematics May 14 '13 at 09:50
@user13814 - from that question it looks like you have both `FruitId` and `CrateId` – Darren May 14 '13 at 09:51
Yes, but it's not important that Table 3 will have all of **Fruit IDs** or **CrateIDs** in it, as In above example "Mango 8888" isn't in table 3 already – Mathematics May 14 '13 at 09:52
@user13814 - you have only shown me table 3, I need to know the other tables you are using so I can help... – Darren May 14 '13 at 09:55
Sir, table structure is very complex and I am not **allowed** to play with them, I just got this table I can work on, thanks – Mathematics May 14 '13 at 09:56

How can I update Crate IDs of List of Fruits in single SQL query in c#

1 Answers1

Linked