Take the 2-minute tour ×
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.

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.

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
1  
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
1  
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 a1.id = @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
show 6 more comments

1 Answer

up vote 0 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,
.....etc
from address a1, address a2
where a1.id = @to and a2.id = @from
share|improve this answer
add comment

Your Answer

 
discard

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.