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...
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;
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 = value1 would be t2.value1, but would be t1.value1 if t2.value1 was missing.
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'
);
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';
I have to add values on click event of button1. I have 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 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??
help..
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
