Syntax of Update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Syntax of Update

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-02-04 : 22:48:34

I want to update a column in a table as follows

update Mytable
set DateCol = (select Min(Datecol) from MyTable A where A.DateCol > DateCol)

The where condition is ambiguous. Is there a syntax construct that will make it more specific so the WHERE clearly references the outer table?

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-04 : 23:13:04

update Mytable
set DateCol = (select Min(A.Datecol) from MyTable A where A.DateCol > Mytable.DateCol)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2003-02-04 : 23:21:45

How can I infer that MyTable.DateCol doesn't refer to the inner MyTable?

Is it because MyTable as A means ONLY as A?

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-05 : 00:09:06

yep, the subquery copy of MyTable is aliased to A so that is the only way of referencing it.

try

select * from MyTable A where MyTable.DateCol = getdate()

and you will get an error.

you can also alias the updated copy of the table so that MyTable isn't available at all

update B
set DateCol = (select Min(A.Datecol) from MyTable A where A.DateCol > B.DateCol)
from MyTable B

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2003-02-05 : 08:36:23

Ah Ha!

Update B
set ...
From MyTable B

Thank you nr.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources