Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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,
from address a1, address a2
where = @to and = @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.

share|improve this question
Wouldn't you have to specify a table alias in the first line, instead of just the table name? I would think what's written is a syntax error. – Jon Seigel Apr 24 '13 at 2:49
@JonSeigel, specifying a table alias on the first line produces a syntax error. This does not. – user606723 Apr 24 '13 at 2:51
Eh, I tried. I guess my SQL Server knowledge doesn't apply to Sybase. Different T-SQL I guess. – Jon Seigel Apr 24 '13 at 2:52
Perhaps to be unambiguous, you'll need to prepend the LHS of the SET clause, e.g. SET a1.col1=a2.col1, a1.col2=a2.col2,... following the syntax in example 3 in the manual – 孔夫子 Apr 24 '13 at 3:20
Oh, how silly of me! You are right of course, using scalar subqueries doesn't make much sense here. Don't know how I was reading the question that I managed to miss that. This one might do better: UPDATE address SET col1 = a2.col1, col2 = a2.col2, ... FROM address a1, (SELECT * FROM address WHERE id = @from) a2 WHERE = @to;. Don't know if Sybase would allow a subquery in that context, though. And certainly the syntax is less elegant than yours, it's just that it might be less ambiguous to the peruser this way. – Andriy M Jul 30 '13 at 22:59
up vote 1 down vote accepted

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,
from address a1, address a2
where = @to and = @from
share|improve this answer
wouldn't this have to be update a1 ... I am trying to do this in SQL Server where it does. Just curious. – cdonner Jul 30 '15 at 15:18

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.