Oracle 11g 中的更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/556072/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update in Oracle 11g
提问by Edwin
I have to update a field in a table. I'm using following queries. Help me, which one is right?
我必须更新表中的字段。我正在使用以下查询。帮帮我，哪个是对的？
update table1
set col1=<value>,col2=<value>....
from table1 t,table2 s
where t.id=s.num
and s.code='abc';
or
或者
update table1
set col1=<value>,col2=<value>....
where table1.id=table2.num
and table2.code='abc';
Which one is right? Or both are incorrect? Please suggest some way...
哪一个是对的？还是两者都不正确？请建议一些方法...
回答by Tony Andrews
Neither is correct. It's not clear from your fragment what you are trying to do, but the syntax to update one table with values from another would be more like:
两者都不正确。从您的片段中不清楚您要做什么，但是使用另一个表的值更新一个表的语法更像是：
update table1
set (col1, col2) =
( select col1, col2
from table2
where table2.num = table1.id
and table2.code = 'abc'
)
where table1.id in (select num from table2);
The final WHERE clause is to prevent updating all non-matched table1 rows with nulls.
最后一个 WHERE 子句是为了防止用空值更新所有不匹配的 table1 行。
Another method that works when table1 is "key preserved"in the query is:
当 table1在查询中“保留密钥”时，另一种有效的方法是：
update
( select table1.id, table1.col1, table1.col2
, table2.col1 as new_col1, table2.col as new_col2
from table1
join table2 on table2.num = table1.id
)
set col1 = new_col1, col2 = new_col2;
回答by Quassnoi
MERGE
INTO table1 t1
USING (
SELECT *
FROM table2
WHERE table2.code = 'abc'
) t2
ON (t1.id = t2.num)
WHEN MATCHED THEN
UPDATE
SET col1 = t2.value1, col2 = t2.value2
Remember to always qualify the SET statement, ie t2.value1. No error is thrown when ambiguous. In other words, SET col1 = value1would be t2.value1, but would be t1.value1if t2.value1was missing.
请记住始终限定 SET 语句，即 t2.value1。不明确时不会抛出错误。换句话说，SET col1 = value1将是t2.value1，但如果t2.value1缺失，它将是t1.value1。
回答by Edwin
Based on your last remark, you need to update table table1 with fixed values that are not in a database table. But only for rows in table1 that match with specific rows in table 2. In that case you can use this statement:
根据您的最后一句话，您需要使用不在数据库表中的固定值更新表 table1。但仅适用于 table1 中与表 2 中特定行匹配的行。在这种情况下，您可以使用以下语句：
update table1 t1
set t1.col1='value',
t1.col2='value2',
t1.col3='value3'
where exists ( select ''
from table2 s
where t1.id=s.num
and s.code='abc'
);
回答by Edwin
I have used the following query:
我使用了以下查询：
update (select col1 from table1 t inner join table2 s
on t.id=s.num where s.code='abc') t1
set t1.col1='value';
it worked fine..
它工作得很好..
But when I used it for updating multiple cols, it produced an error:
但是当我用它来更新多个列时，它产生了一个错误：
Missing right parenthesis.
缺少右括号。
Could you help...
你能帮忙吗...
update (select col1,col2,col3 from table1 t inner join table2 s
on t.id=s.num where s.code='abc') t1
set t1.col1='value',t1.col2='value2',t1.col3='value3';
回答by Edwin
I have to add values on click event of button1. I have used the following code:
我必须在 button1 的点击事件上添加值。我使用了以下代码：
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
val = TextBox1.Text
If RadioButton1.Checked = True Then
rcvd = RadioButton1.Text
ElseIf RadioButton2.Checked = True Then
rcvd = RadioButton2.Text
End If
If RadioButton5.Checked = True Then
type = RadioButton5.Text
ElseIf RadioButton6.Checked = True Then
type = RadioButton6.Text
ElseIf RadioButton7.Checked = True Then
type = RadioButton8.Text
ElseIf RadioButton9.Checked = True Then
type = RadioButton9.Text
ElseIf RadioButton10.Checked = True Then
type = RadioButton10.Text
End If
Try
XXX = "update(select rcvd,amount,instype,chq,ucode,uname,remarks from fapark04 f inner join sumast04 s on f.party=s.account where s.abnmn=' " & val & " ' ) fa set fa.rcvd=' " & rcvd & " ', fa.amount= " & TextBox5.Text & " ,fa.instype='" & type & " ',fa.chq= " & TextBox9.Text & " ,fa.ucode=' " & TextBox12.Text & "',fa.uname='" & TextBox13.Text & "',fa.remarks='" & TextBox14.Text & "' "
cmd1 = New OracleCommand(XXX, con)
cmd1.ExecuteNonQuery()
Catch ex As Exception
MsgBox("A Run time error occured!!!", ex.ToString)
End Try
End Sub
This does not updates the rows but when same query is fired at the backend it updates the rows.
这不会更新行，但是当在后端触发相同的查询时，它会更新行。
When updated from front end it shows 0 rows updated. Why is it so??
从前端更新时，它显示更新了 0 行。为什么会这样？？
help..
帮助..
回答by Arun Narang
In your query, you are using two tables in where clause condition. So you have to either join or subquery or merge type, use so that u can get correct output
在您的查询中，您在 where 子句条件中使用了两个表。所以你必须加入或子查询或合并类型，使用以便你可以获得正确的输出
