Menu
I have two tables with same columns. i want to update table1 records whose status is 'Linked' by the corresponding values from table2.
table 1
ID STATUS VOUCHER
'T010000020 Not Linked null
'T010000021 Linked null
'T010000024 Not Linked null
'T010000026 Linked null
table 2
ID STATUS VOUCHER
'T010000020 Not Linked null
'T010000021 Linked 11234
'T010000024 Not Linked null
'T010000026 Linked 5423
511
It is pretty straightforward to use the UPDATE from SELECT statement in this instance. You can first use the SELECT statement to fetch the reference column and target column values. Next, you will perform slight changes in your query, and it will prepare an UPDATE statement as shown below.
The SELECT statement with the FOR UPDATE clause ( SELECT FOR UPDATE statement) selects the rows of the result set and locks them. SELECT FOR UPDATE lets you base an update on the existing values in the rows, because it ensures that no other user can change those values before you update them.
Like SELECT , the UPDATE statement can have a subquery in several places or clauses. In an UPDATE , the two clauses in which subqueries are used most commonly are SET and WHERE . The SET clause is where we define the new value for the column being modified by the UPDATE .
UPDATE Table1 t1
SET Voucher = (SELECT Voucher FROM
Table2 t2 WHERE t2.Id = t1.Id
and t2.Status = 'Linked')
WHERE Status = 'Linked'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
