Tuesday, July 28, 2009

T-SQL ordered Update

I was trying to do this update, but I wanted done in a specific order. I have found two solutions. One is to use a Common Table Expression, which would look like this:
WITH q AS (
SELECT x,y,z FROM SomeTable
ORDER BY z
) UPDATE q
SET x=y+z
Strangely enough this updates the SomeTable table in the order of z.

However, I had to do it in SQL 2000, so Common Table Expressions were not available. The only other solution I could think of was a temporary table with an identity column:
CREATE #temp (
realid INT IDENTITY(1,1),
x INT,
y INT,
z INT
)
INSERT INTO #temp (x,y,z)
SELECT x,y,z FROM SomeTable
ORDER BY z
UPDATE #temp SET x=y+z


The most difficult part of the task was to not think procedurally. SQL is a functional language and one must think differently. Weird is I knew that I had to think in a functional way and I said it out loud when I started the task. It took a few hours of trying to create recursive functions or emulate loops in SQL before I could change my thinking. Using SQL in a procedural way, even if possible, means using something in the wrong way.

0 comments: