问题:
I have an array in powershell
$FruitNameInventory = Apple, Banana, Orange
I have a table in SQL
ID|Name |FruitOrder |MatchInventory
1 |Sar...
可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:
问题:
I have an array in powershell
$FruitNameInventory = Apple, Banana, Orange
I have a table in SQL
ID|Name |FruitOrder |MatchInventory
1 |Sarah|Apple, Banana, Orange, Peach, Mangoes|
2 |John |Apple, Banana |
3 |Mary |Peach, Mangoes |
4 |Mark |Mangoes |
For every "FruitNameInventory" Array item that is contained within the "FruitOrder" SQL Column, I want to append the value of the array item of "FruitNameInventory " to the "MatchInventory" Column. Indicating Inventory Matches.
Question: Can I have one SQL statement with both select and update? Please see logic and further explanation:
Foreach ($FruitName in $FruitNameInventory){
SQL Query: "SELECT * FROM FRUITORDERDB
WHERE FruitOrder LIKE '%$FruitName%'
****How to add UPDATE statement into this, so I can update the
MatchInventory by appending $FruitName?***
}
回答1:
I want to append the value of the array item of "FruitNameInventory " to the "MatchInventory" Column. Indicating Inventory Matches.
This is just a matter of selecting the relevant record and handling the string concatenation.
Try:
UPDATE
FRUITORDERDB
SET MatchInventory =
CASE
WHEN MatchInventory IS NULL THEN ?
ELSE CONCAT(MatchInventory, ',', ?)
END
WHERE FruitOrder LIKE CONCAT('%', ?, '%')
Where the question mark stands for the value that comes from your Powershell variable.
CONCAT()
and CASE
are standard ANSI SQL keywords that are supported by most RDBMS (when not available, there is usually an identical vendor-specific functionality).
It could also be a good idea to ensure that the value that you add to the column is not already there (eg if you run the query several times), for this you can alter the WHERE
clause:
WHERE
FruitOrder LIKE CONCAT('%', ?, '%')
AND MatchInventory NOT LIKE CONCAT('%', ?, '%')
Also, let me suggest a alternative way to ensure that a string is part of a CSV list:
WHERE CONCAT(',', FruitOrder, ',') LIKE CONCAT('%,', ?, ',%')
Although less efficient, this expression is safer in the sense that it ensures that a full word matches (eg it will properly handle the case when the CSV field contains something like 'test,testing'
and you are looking for 'test'
only).