Skip to main content

UPDATE...FROM (Sql Server 2005/2008)

I keep forgetting how this concept works each time I go to do it. I always start off with a simple update, then find I need a quick join to another table to use a value there to complete the update. What I forget is how the table referenced after UPDATE relates to the tables/columns in the FROM (and SET) areas.

From what I understand now, the "x" in "UPDATE x" either directly matches a table referenced in the FROM clause, or directly matches an alias for a table in the FROM. In the example below, there are 3 updates that are functionally equivalent.

CREATE TABLE #table1 (t1_pk int, t1_months smallint);
CREATE TABLE #table2 (t2_pk int, t2_t1_pk int, t2_starts datetime, t2_ends datetime);
INSERT INTO #table1 VALUES (1, 2);
INSERT INTO #table1 VALUES (2, 5);
INSERT INTO #table2 VALUES (1, 1, '2000-01-01 11:45 PM', NULL);
INSERT INTO #table2 VALUES (2, 1, '2001-01-01 02:50 AM', NULL);
INSERT INTO #table2 VALUES (3, 2, '2004-01-04 06:45 PM', NULL);
INSERT INTO #table2 VALUES (4, NULL, '2005-01-08 03:21 PM', NULL);

SELECT * FROM #table1;
SELECT * FROM #table2;

/* Version 1 */
UPDATE #table2
SET t2_ends = DATEADD(month, t1_months, t2_starts)
FROM #table2
INNER JOIN #table1 ON t1_pk = t2_t1_pk
;

/* Version 2 */
UPDATE t
SET t2_ends = DATEADD(month, t1_months, t2_starts)
FROM #table2 AS t
INNER JOIN #table1 ON t1_pk = t2_t1_pk
;

/* Version 3 */
UPDATE #table2
SET t2_ends = DATEADD(month, t1_months, t2_starts)
FROM #table1
WHERE t1_pk = t2_t1_pk
;

SELECT * FROM #table2;

DROP TABLE #table1;
DROP TABLE #table2;

Abbreviated results:

--#table1 BEFORE
t1_pk t1_months
----------- ---------
1 2
2 5

--#table2 BEFORE
t2_pk t2_t1_pk t2_starts t2_ends
----------- ----------- ----------------------- -----------------------
1 1 2000-01-01 23:45:00.000 NULL
2 1 2001-01-01 02:50:00.000 NULL
3 2 2004-01-04 18:45:00.000 NULL
4 NULL 2005-01-08 15:21:00.000 NULL

--#table2 AFTER (any of the 3 updates)
t2_pk t2_t1_pk t2_starts t2_ends
----------- ----------- ----------------------- -----------------------
1 1 2000-01-01 23:45:00.000 2000-03-01 23:45:00.000
2 1 2001-01-01 02:50:00.000 2001-03-01 02:50:00.000
3 2 2004-01-04 18:45:00.000 2004-06-04 18:45:00.000
4 NULL 2005-01-08 15:21:00.000 NULL

This was done against Sql Server 2008, and all 3 update statements had identical query plans too.
Labels:

Comments

Post a Comment

Popular posts from this blog

Live Migration between domains

For those of you like me who aren't experts at all things Active Directory (AD) and Hyper-V Live Migration (LM) permissions, it can be enough of a pain to LM a Virtual Machine (VM) between domains that you simply decide to take the VMs offline to affect the move. See, I only tolerate AD because it's required for LM'ing VMs; there isn't a choice. (It's also required for Windows Clusters, but that's a different topic.) But I figured it out. My back-story is that we setup a cluster using Windows 2012 r1 as the AD Domain Controller (DC) and Hyper-V Server 2012 r1 for the VM hosts. Then we decided we wanted to use r2 for the AD DC and Hyper-V hosts. Upgrading Hyper-V was easy. But I found that there's some unresolved Microsoft bug with Windows Clustering when upgrading the AD DC from Windows 2012 r1 to Windows 2012 r2--- clustering simply doesn't work correctly anymore . So we gave up and created a from-scratch Windows 2012 r2 AD DC then made a new cluster...
Post a Comment
Read more

SqlBulkCopy and the "colid" error

I thought there was a page explaining this somewhere out there on the Internet, but I can't find it anymore. So here's what I re-discovered. When you try to insert the rows from a DataTable and the data in one of the columns of one of the rows is too big to fit into the destination column in the database, you get a SqlException with this error message: "Received an invalid column length from the bcp client for colid N." (Where "N" is a number.) It doesn't tell you which row, and it's a pain to figure out what column to look at. To determine what column it is referring to, you first need to get a listing of all columns in the table, listed in the order as they have been defined in the database. Next, you remove any columns in the list that are not represented in SqlBulkCopy.ColumnMappings (the order of the column mappings is irrelevant). The list that remains is what "colid" is referring to, with the first column corresponding to colid ...
Post a Comment
Read more

Outlook 2007/2010 Search Folders using email address domain

As of May 2010, the Beta of Outlook 2010 still hasn’t overcome this problem. I’m surprised this glaring omission has been left unfixed. Maybe Outlook is maintained by contractors? I have what I consider to be a simple need. I want a “Search Folder” that shows me all the email related to a particular client. What works well for me is a query that finds any email with the client name in the subject, or any email that involves an email address (from/to/cc) from the client's email domain. Back in Thunderbird, it was simple to setup a rule for this. Outlook can't do it. (I'd go back to Thunderbird, but I have to get calendaring working first.) What doesn't work When you edit the criteria for a Search Folder, on the “Messages” tab, the fields you want appear to be represented, but the way things work is wrong. All the criteria specified must be true, not any; they went with “AND” where I need “OR”. The other problem is that the “From…” and “Sent To…” fields use a “starts-with...
1 comment
Read more