T-sql – update statement with self join

join;sybaset-sqlupdate

I needed to copy information from one row of a table to another.
I noticed I could do the following:

update address
set col1 = a2.col1,
col2 = a2.col2,
.....etc
from address a1, address a2
where a1.id = @to and a2.id = @from

However, the above SQL seems kind of ambiguous.
How do I know it's going to update row @to with data of @from and not the otherway around?

Edit: Note: while I have not done extensive testing, this query does seem to work as I try it against our development database. It simply updates the one @to row with results from the @from row.

Best Answer

To be less ambiguous you can prepend the alias in the set clause. (Taken from 孔夫子 in the comments, who did not submit an answer.)

update address
set a1.col1 = a2.col1,
a1.col2 = a2.col2,
.....etc
from address a1, address a2
where a1.id = @to and a2.id = @from

PERSPECTIVE #1

When you update by Primary Key only in InnoDB, there is a rare but possible occasion when the clustered index (aka gen_clust_index) can get locked.

I once answered three posts from one individual on this subject

Please read through these carefully. The poster of these question found his own workaround based on the seeing InnoDB Clustered Index Locking behavior. Sadly, he did not post what the workaround was.

In addition, when you see the queries running slow, log into mysql and run SHOW ENGINE INNODB STATUS\G and starting looking for locks in the Clustered Index.

PERSPECTIVE #2

I see you commented out innodb_log_file_size. You have it at 5MB, the default. Since innodb_buffer_pool_size is set to 1G, innodb_log_file_size needs to be at 256M. Click here to go about setting innodb_log_file_size to 256M.

PERSPECTIVE #3

I see you are not using innodb_file_per_table. You may want to use it in order to have table updates done specifically for the one table with a million rows. Click here to see how to Clean Up InnoDB infrastructure to use innodb_file_per_table.

Sql-server – Find last (max) value according to TimeStamp using update method

Your question is not clear. You are trying to update the value of some table based on a value of another? This can be solved using something like the query below:

In SQL Server 2005 or newer:

with my_data_cte (
  select row_number() over (order by timestamp desc) as sequence,
  pk_vv
from (
  select timestamp, pk_v from tb1
  union all
  select timestamp, pk_v from tb2
 )
)
update my_table
set value = pk_v
from my_table mt
  inner join my_data_cte ct on mt.pk_v = mt.pk_v
where sequence = 1

Best Answer

Related Solutions

Mysql – Innodb UPDATE performance

PERSPECTIVE #1

PERSPECTIVE #2

PERSPECTIVE #3

Sql-server – Find last (max) value according to TimeStamp using update method

Related Question