update in oracle 11g

Question

i hv to update a field in a table. i m using following querries. 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...

Answer 1

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.

Another method that works when table1 is "key preserved" in the query is:

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;

Answer 2

MERGE
INTO table1 t1
USING (
 SELECT *
 FROM table2
 WHERE table2.code = 'abc'
) t2
ON t1.id = t2.num
WHEN MATCHED THEN
  UPDATE
  SET col1 = value1, col2 = value2

Answer 3

i hv used the following querry:

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. cud u 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';

Answer 4

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:

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'
);

Answer 5

i have to add values on click event of button1.I hv used the following code:

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 doesnot updates the records but when same querry is fired at the backened it updates the rows. when updated from front end it shows 0 rows updated. why is it so?? help..