如何更新有关同一个表的许多记录 [英] How to update many records about same table

问题描述

我有这张桌子



用户| column1 |第2栏

-----------------------------

彼得| a |

彼得| b |

彼得| c |

josue | a |

josue | b |



我想更新column2，其中column1的记录作为函数中的参数，返回column2的值，但我需要按用户过滤，这种情况下只有用户peter的记录

  update  table1 
  set  column2 = dbo.fn_function（t.column1）
 来自 table1 t 
 其中 t.user = '  peter' 

更新执行显示下一个错误：子查询返回多个值



我做错了什么？如果fn_function返回一个值？



没有from不起作用显示相同的错误



< pre lang =SQL> update table1
set column2 = dbo.fn_function（column1）
其中 user = ' peter'

解决方案

您不需要从行

 更新 table1 
  set  column2 = dbo.fn_function （t.column1）
 
摆脱 此行
  来自 table1 t  
 
 其中 t.user = '  peter' 

因为你正在更新同一张桌子检查要求= peteron。

你确定你的函数调用没有为column1返回多个响应吗？看起来该函数只使用参数column1，并返回多个结果，因此查询不知道要使用哪个函数结果。

嗯...



我在我的服务器上重新创建了这个

  update  dbo.SalesmanList 
  set  lengthofRepCode = dbo.tester（RepCode）
 其中名称= '  Alan Lese' 

dbo.tester函数不引用任何表，只需获取给定的varchar数据参数并返回字符串的长度，然后更新表列oldaocpersno到任何地方。我为Alan Lese名称设置了两条记录，但在RepCode列中有不同长度的数据，它对我来说非常适合。



我真的需要看到你的功能我想进一步帮助你，抱歉。

I have this table

user | column1 | column2
-----------------------------
peter | a |
peter | b |
peter | c |
josue | a |
josue | b |

I want to update the column2 with the records of column1 as parameter in a function, which return a value for column2, but i need filter by user, in this case only the records with the user peter

update table1
set column2 = dbo.fn_function(t.column1)
from table1 t
where t.user='peter'

The update execute shows the next error: "subquery return more than one value"

What I'm doing wrong? if the fn_function return one value?

Not works without "from" shows the same error

update table1
set column2 = dbo.fn_function(column1)
where user='peter'

解决方案

You don't need the "from" line

update table1
set column2 = dbo.fn_function(t.column1)

get rid of this line
from table1 t

where t.user='peter'

since you are updating the same table you are checking the requirement "=peter" on.

Are you sure that your function call doesn't return more than one response for "column1"? It looks like the function is only using the parameter "column1", and getting multiple results back, so the query doesn't know which function result to use.

Hmmm...

I've recreated this on my server as so

update dbo.SalesmanList
set lengthofRepCode = dbo.tester(RepCode)
where Name = 'Alan Lese'

the dbo.tester function doesn't reference any tables, just takes the varchar data parameter given and returns the length of the string, and then updates the table column oldaocpersno to whatever that is. I set up two records for the Alan Lese name, but with different length data in the RepCode column, and it worked perfect for me.

I'd really need to see your function i think to help you further, sorry.

这篇关于如何更新有关同一个表的许多记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！