SQL + Powershell :Select statement when WHERE matches perform an Update to a column within a row that matches

问题: 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).

  • 发表于 2019-03-02 20:24
  • 阅读 ( 202 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除