Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at CFUNITED 2008 (Washington, D.C.) with:

Using A SQL JOIN In A SQL UPDATE Statement (Thanks John Eric!)

By Ben Nadel on
Tags: SQL

I love learning new, cool stuff about SQL. It doesn't happen all that often (most of my SQL is fairly simple), but every now and then someone shows me something that just rocks my world, whether it be the power of Indexing or just something as simple as using UNION ALL instead of UNION. Last week, John Eric dropped a bomb shell on me, demonstrating how to update a table in conjunction with a SQL JOIN statement.

I have known for a long time that you could update a SQL View in Microsoft SQL Server (back when I used to use Views), so it makes sense that you could update a JOIN, but it never occurred to me to try this. Not only did it not occur to me, but the syntax used to do this is very strange to me (although now that I have stared at it for a long time, it's starting to make more sense).

Anyway, enough talk, let's take a look at this in action. Since I don't have any tables ready to play with, I have created three in-memory SQL tables: boy, girl, and relationship. The boy table lists boys, the girl table lists girls, and the relationship table lists out romantic relationships between the two (what can I say, I am a romantic fool at heart). Then, what I am going to do is UPDATE the boy table based on certain relationship criteria - in this case, anyone who has dated Winona Ryder is clearly a stud and should be flagged as such.

  • <cfquery name="qUpdateTest" datasource="#REQUEST.DSN.Source#">
  • <!--- Declare in-memory data tables. --->
  • DECLARE
  • @boy TABLE
  • (
  • id INT,
  • name VARCHAR( 30 ),
  • is_stud TINYINT
  • )
  • ;
  •  
  • DECLARE
  • @girl TABLE
  • (
  • id INT,
  • name VARCHAR( 30 )
  • )
  • ;
  •  
  • DECLARE
  • @relationship TABLE
  • (
  • boy_id INT,
  • girl_id INT,
  • date_started DATETIME,
  • date_ended DATETIME
  • )
  • ;
  •  
  •  
  • <!---
  • Populate the boy table with some information.
  • Notice that as I populate the IS_STUD column, all
  • the values are going to be ZERO (meaning that these
  • dudes are not very studly). This will be updated
  • based on the relationship JOIN.
  • --->
  • INSERT INTO @boy
  • (
  • id,
  • name,
  • is_stud
  • )(
  • SELECT 1, 'Ben', 0 UNION ALL
  • SELECT 2, 'Arnold', 0 UNION ALL
  • SELECT 3, 'Vincent', 0
  • );
  •  
  •  
  • <!--- Populate the girl table with some information. --->
  • INSERT INTO @girl
  • (
  • id,
  • name
  • )(
  • SELECT 1, 'Maria Bello' UNION ALL
  • SELECT 2, 'Christina Cox' UNION ALL
  • SELECT 3, 'Winona Ryder'
  • );
  •  
  •  
  • <!--- Populate the relationship table. --->
  • INSERT INTO @relationship
  • (
  • boy_id,
  • girl_id,
  • date_started,
  • date_ended
  • )(
  • SELECT 1, 1, '2007/01/01', NULL UNION ALL
  • SELECT 1, 3, '2004/09/15', '2005/06/15' UNION ALL
  • SELECT 2, 1, '2006/05/14', '2006/05/23'
  • );
  •  
  •  
  • <!---
  • Update the in-memory table. Here, we are going to join
  • the boy, girl, and relationship table to see if any of
  • the boys have been studly enough to date Winona Ryder.
  • If so, that BOY record will be updated date with the
  • is_studly flag.
  • --->
  • UPDATE
  • b
  • SET
  • b.is_stud = 1
  • FROM
  • @boy b
  • INNER JOIN
  • @relationship r
  • ON
  • b.id = r.boy_id
  • INNER JOIN
  • @girl g
  • ON
  • (
  • r.girl_id = g.id
  • AND
  • g.name = 'Winona Ryder'
  • )
  • ;
  •  
  •  
  • <!---
  • To see if the update has taken place, let's grab
  • the records from the boy table.
  • --->
  • SELECT
  • id,
  • name,
  • is_stud
  • FROM
  • @boy
  • ;
  • </cfquery>
  •  
  •  
  • <!--- Dump out the updated record set. --->
  • <cfdump
  • var="#qUpdateTest#"
  • label="Updated BOY Table"
  • />

Notice how the SQL UPDATE statement is JOINing the @boy, @girl, and @relationship table using INNER JOINs and limiting it to boys who have dated Winona Ryder. The update is made to the result of that JOIN and then we are selecting all the rows from that updated @boy table (to see that it works). Running the above code, we get the following CFDump output:


 
 
 

 
SQL UPDATE Used In Conjunction With SQL JOIN Statement  
 
 
 

Notice that the Ben record was updated to reflect the Studly property. This is pretty cool stuff. Frankly, I haven't even ever used a FROM clause in my UPDATE statement (unless part of a sub-query). This is going to give me some cool stuff to explore.

Thanks John Eric!




Reader Comments

Ben,

As general rule you can write all your UPDATE, DELETE query the same way you can write SELECT Query. They have same structure of syntax. You could also DELETE using JOIN as well. Try following query you will love it.

DELETE tableName
FROM tableName tn
INNER JOIN JoinedTable jn ON jn.col = tn.col
WHERE jn = someval

I see where you are going. Similar thing happened to me.

I was biggest fan of ColdFusion and can not stop thinking about it. One day, I started to learn SQL and here it goes. I could not come back to ColdFusion from SQL.

I guess, Once you go SQL...

Pinal

Reply to this Comment

@Pinal Dave,

Cool stuff. I didn't know you could use this in DELETE as well. Awesome stuff. One of the things I have always wanted to do is create a table alias in the UPDATE statement, but it has always failed due to Syntax error. That was before I knew you could do a FROM clause in the UPDATE statement. This should make things much easier, especially when dealing with an UPDATE that uses a sub-query.

Good stuff!

Reply to this Comment

Pinal --> For me it's been using them both together. I've seen some people write a lot of CF code to do something they could do in just a few SQL commands passed off via CFQuery.

Reply to this Comment

I am with Allen on this one. I don't think one is necessarily better than the other - they do different things. They work together to create harmony. The trick is to know when to leverage the powers of each tool.

Reply to this Comment

How odd that I would stumble across this. Recently I started using UPDATE...SET...FROM to persist an array of objects in one SQL statement.

Essentially, I pass an array of components to a persistence object, and construct an in-memory table within the UPDATE statement, looping through the array of objects to be persisted and adding a UNION ALL after every loop iteration. Except the last one, of course.

Works well, and is much, much faster than using one SQL statement per loop iteration. Although, I'll admit upfront that my measurement process consists of running the app and saying "Well, that seemed a lot faster". :)

Reply to this Comment

@Matt,

If you think about applications from a "user experience" standpoint, it's the "that runs faster" moment much more important than any numeric reading? As long as you perceive it to be faster, that's all that counts.

Reply to this Comment

Ben and Pinal

Hi guys!

I was wondering if this technique could be modified to set is_stud to the value of one of the other tables in the join (say, g.id) instead of a static 1.

This would be very useful for me in a situation I've been finding myself in frequently.

I have a table of countries (country_id, country_name etc.) and a new table (people) with a country_name field that matches up with countries.country_name

I would like to set people.country_id (which for all records is currently set to zero since the data is new) equal to countries.country_id where the country names in each table match.

Is this achievable with just SQL and not CF as I have been doing?

I'm just not sure of values can be returned from the join conditions being met.

Thanks and keep up the good help :)

Steve

Reply to this Comment

@Steve,

Yes, you can update one table using the value in another table in the manor. Create the JOIN that you think is appropriate and then set one column to the other.

Reply to this Comment

Effin ay, mang! You should teach a class! As far as blogs go, this one didn't suck. And I learned something. kudos.

Reply to this Comment

I am a fan of creating an array via XML, parsing it into a table, then using the above set-based insert/update/delete method.

By using XML and the above method, you can drop the execution time of multiple inserts in one transaction drastically. For example, if you had to loop over an insert statement; try changing it to creating XML with the same loop, then having SQL parse the XML into a temporary table and doing your insert via a select statement on the temporary table.

It is also possible to create a "CRUD" like stored procedure by adding a variable in the nodes to flag whether it is an insert, update or delete.

If anyone is interested in the XML CRUD stored procedure, let me know and I will see if I can dig it out of one of my past projects.

Reply to this Comment

@eXcalibur.lk,

I am sorry, I don't follow what you are saying; but, I am intrigued. What XML are you talking about? And how are you turning that XML into an INSERT / UPDATE statement?

Reply to this Comment

I am on holidays in another state at the moment, so I can not get my previous work; however here is an approach that is a little bit less verbose than my example: http://pratchev.blogspot.com/2008/11/import-xml-file-to-sql-table.html

When I get back, I will post my example. I did a proof of concept at my last job by looping over a cfquery with a sql insert and compared that to looping over an xml document, then doing the insert via sql parsing and table to table inserts. The results even surprised me.

Reply to this Comment

@Andrew,

Oh wow, that's pretty cool. I've never seen that before. Do you know what databases this is valid for?

Reply to this Comment

@Ben,

The functionality is for MS SQL (I think form 2005 on), I have not been able to find anything similar for other DBMSs; mind you have not been looking to hard, as currently all my projects have been with MS SQL.

Reply to this Comment

Hi Ben, thanks for posting this, hope you don't mind I've put a link from my (rarely used) blog to your page and thought you'd like to know that I just used this technique to update 132 million records in an hour!

Reply to this Comment

@J.D.

Sure can, Ben shows how in the blog:

INSERT INTO @girl
(
id,
name
)(
SELECT 1, 'Maria Bello' UNION ALL
SELECT 2, 'Christina Cox' UNION ALL
SELECT 3, 'Winona Ryder'
);

If you need the values to come from a table, then substitute the static information for column names and add a from clause; for example:

INSERT INTO @girlInRelationship
(
id,
name
)(
SELECT girl.id, girl.name
FROM girl
INNER JOIN relationship
ON girl.id = relationship.girl_id
);

** I am assuming Ben's code is for MySQL, which I don't normally code in, as such the syntax for my example may be off.

Reply to this Comment

thanks Andrew Bauer :D

may I ask do you know does SQL can do "split" like C# can do?

in my column save data like this

Table A

SHOPNO GOODS
101`102 BANANA

Table B

SHOPNO SHOPNAME
101 SUN
101 MOON

is it possible to split A.SHOPNO then compare with B.SHOPNO to get B.SHOPNAME?

I hope can get result like this

SHOPNO SHOPNAME GOODS
101'102 SUN`MOON BANANA

does SQL could do split like other code languae can do?

thanks alot

Reply to this Comment

@JD,

I am glad that I could help with my previous reply.

With your question; it is possible to do with stored procedures and functions, but I would not suggest it. It is not a good idea to hold multiple key values in the same column.

Having multiple keys in a column leads to data integrity issues (like will banana exist in your database, if the links to shops 101 & 102 are removed?). I would suggest changing it into 3 tables:

Table A (Goods)
GoodsID, GoodsName
1, Banana
2, Apple

Table B (Shops)
ShopNo, ShopName
101, Sun
102, Moon

Table C (ShopGoods or Inventory) "Join Table"
ID, GoodsID, ShopNo
1, 1, 101
2, 1, 102
3, 2, 102

This is the basic setup for a many-to-many relationship between two entities (tables). By adding the join table you can then manage the relationships with simple Insert/Update/Delete statements. Also the result you are after is achieved with an inner join (however over multiple rows):

SELECT B.ShopNo, B.ShopName, A.GoodsName
FROM Goods AS A
INNER JOIN ShopGoods AS C
ON A.GoodsID = C.GoodsID
INNER JOIN Shops AS B
ON B.ShopNo = C.ShopNo

Results:
ShopNo, ShopName, GoodsName
101, Sun, Banana
102, Moon, Banana
102, Moon, Apple

@Ben,

Sorry for the long comment. I realize that I could of answered the question directly, and I don't want to hi-jack your blog comments with essay responses of my own; however I have seen too many people fall into the trap of getting a table's rows to describe more than one entity, and am hoping to help with an alternative approach.

Reply to this Comment

hi, Andrew Bauer

again thanks for your reply !!

I been google for find the answer

people just siad like you just told

not suggest to save mutiple value in same column..

but the schema and exist data just save like this format.. ( I can't change schema... which old Data exist many data row already)

Table A

SHOPNO GOODS
101`102 BANANA

if I really need to find a way to retrieve the SHOPNAME..is the best way by other code language not SQL?

thank you very much!!

Reply to this Comment

@JD,

I can understand the pain of not being able to change the schema and I know that a well structured schema saves you from a lot of headaches.

Personally, I would flat-out refuse to work with this type of database schema, opting to volunteer rewriting the structure and creating ETL (Extract Transfer Load) scripts to move the data to the new schema. In my opinion, a couple of days for writing a schema/ETL scripts and changing code that my already rely on the current structure, is much better to creating a hack that will cause you troubles down the line.

If you absolutely must continue with the current structure; then the quickest and easiest approach would be to query Table A in ColdFusion, loop over each record, then loop over each value in the ShopNo list of IDs (ColdFusion has some useful list functions), query Table B with the list values, then write the desired values to a custom query result set. This approach is very verbose and will make it difficult to maintain, not too mention it will be very slow as more records are added.

I hope for your sanity that you manage to convince whoever to let you change the schema.

Reply to this Comment

hi thank you Andrew Bauer

I just tried to convince them to let me change the schema , but ... in vain , for some reason they just want this kind of schema to save data..

maybe I will try the way ( use other code languae ) to get the result I need

thanks for your patient and suggestion !!

thanks alot

Reply to this Comment

I love the style and look of your website blog. What blogging application are you using? I see the design was inspired by Tim Ferriss. Could you please point me in the right direction.

PS. Thanks for the comments in SQL Joins it helped solve my problem.

Thank you in advance. Mike (Smile)

Reply to this Comment

Great blog! Saved me an hour + of typing... I had updated a table in my development world and wanted to use some of the field data to update the live table counterpart.

Reply to this Comment

sorry for asking a sql question (I am sorry for if it's ok not to ask in here)

I spend 1 week to think out how to finger out this soluction

SELECT SHOPNO,HWSNO,BDATE,HPYNO FROM ECRPYHS
RESULT:
SHOPNO HWSNO BDATE HPYNO
A 01 2004/06/15 01
A 01 2004/06/15 01
A 01 2004/06/15 03
A 01 2004/06/20 03
A 01 2004/06/20 03
A 01 2004/06/20 01

SELECT SHOPNO,HWSNO,BDATE,HSALE_ID FROM ECRHDHS
RESULT:
SHOPNO HWSNO BDATE HSALE_ID HASTOTAL
A 01 2004/06/15 ALLEN 10
A 01 2004/06/15 ALLEN 5
A 01 2004/06/15 NAOMI 10
A 01 2004/06/20 NAOMI 10
A 01 2004/06/20 NAOMI 5
A 01 2004/06/20 ALLEN 10

SELECT PYNO,PYID FROM PAYINFO

RESULT:
PYNO PYID
01 TWD
02 CNY
03 USD
04 JPY
05 HKD
06 KRW
07 GBP
08 AUD
09 CRD

WOULD LIKE TO HAVE THIS RESULT THEN NEED TO BINDING WITH GRIDVIEW

HSALE_ID TWD CNY USD JPY HKD KRW GBP AUD CRD
ALLEN 25 0 0 0 0 0 0 0 0
NAOMI 0 0 25 0 0 0 0 0 0

THE QUESTIONS I WOULD LIKE TO ASK IS
1 ASSUME I DON'T KNOW HOW MANY KINDS OF PAYINFO ( PAYINFO.PYNO) HOW TO SETTING BoundField FOR GRIDVIEW?

2 HOW TO JOIN PAYINFO THEN COUNT EACH KIND OF PAYMENT TYPE THEN JOIN TO MAIN TABLE ?

IT'S NOT PROBLEM FOR ME TO JOIN ECRPYHS WITH ECRHDHS
THEN I HAVE PROBLEM TO JOIN WITH PAYINFO ...

PLEASE HELP.. OR GIVE ME A EXAMPLE TO KNOW HOW TO DO
THANK YOU VERY MUCH!!

Reply to this Comment

Hi J.D,

If you can join ECRPYHS and ECRHDHS, it should be easy to join PAYINFO as well. It looks like PAYINFO.PYNO is related to ECRPYHS.HPYNO

Once you have the joined table, you can use the crosstab stored procedure below to create the cross tabbed table.

You can then tell gridview to automatically generate columns with the tag AutoGenerateColumns="True". You can then configure the columns via sub routines if necessary.

I do this quite often and find it invaluable.

Common syntax for sp_crosstab is;
EXEC [dbo].[sp_Crosstab]
@DBFetch = 'table or view to be cross tabbed - can be a temp table',
@DBField = 'Field to become column headings - PYID in your case',
@PCField = 'Field to be totaled - HASTOTAL in your case',
@PCBuild = 'COUNT, SUM, MIN, MAX or AVG'

I'm not sure where I get this procedure from, so I'm not sure who to give credit to. It is certainly very useful though!

Cheers
Dave

P.S - Sorry for the long post.

--------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_Crosstab]
@DBFetch varchar(4000), --specifies the set of data to summarize
@DBWhere varchar(2000) = NULL, --is optional, specifies a WHERE clause to filter the data specified by the @DBFetch parameter
@DBPivot varchar(4000) = NULL, --is optional, specifies the column names to appear in the result set
@DBField varchar(100), --specifies the column containing the data that becomes result set column names
@PCField varchar(100), --specifies the column to be aggregated.
@PCBuild varchar( 20), --specifies the aggregation to be performed (COUNT, SUM, MIN, MAX or AVG)
@PCAdmin varchar( 20) = NULL, --is optional, specifies a result set column name to represent null values
@DBAdmin int = 0, --is optional, specifies whether any additional columns should be added to the result set for totals
@DBTable varchar(100) = NULL, --is optional, specifies a table name for the result set
@DBWrite varchar(160) = NULL, --is optional, specifies a database name for the @DBTable parameter
@DBUltra bit = 0 --is optional, specifies whether the result set table (if specified) should be dropped (if it already exists) before saving the result set. A value of zero (0) means the table is dropped and created again. A value of one (1) means the result set is to be appended to an already existing table
AS

SET NOCOUNT ON

DECLARE @Return int
DECLARE @Retain int
DECLARE @Status int

SET @Status = 0

DECLARE @TPre varchar(10)

DECLARE @TDo3 tinyint
DECLARE @TDo4 tinyint

SET @TPre = 'tbl'

SET @TDo3 = LEN(@TPre)
SET @TDo4 = LEN(@TPre) + 1

DECLARE @DBAE varchar(40)

DECLARE @Task varchar(8000)

DECLARE @Bank varchar(4000)

DECLARE @Cash varchar(2000)

DECLARE @Rich varchar(2000)

DECLARE @DBAI varchar(4000)
DECLARE @DBAO varchar(8000)
DECLARE @DBAU varchar(2000)

DECLARE @Name varchar(100)
DECLARE @Same varchar(100)

DECLARE @Home varchar(160)

DECLARE @Some varchar(20)

DECLARE @Work int

DECLARE @Wink int

SET @DBAE = '##Crosstab' + RIGHT(CONVERT(varchar(10),@@SPID+100000),5)

SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @DBAE + CHAR(39) + ') DROP TABLE ' + @DBAE

--PRINT @Task

EXECUTE (@Task)

CREATE TABLE #DBAT (Work int IDENTITY(1,1), Name varchar(100))

SET @Bank = @TPre + @DBFetch

IF NOT EXISTS (SELECT * FROM sysobjects WHERE RTRIM(type) = 'U' AND name = @Bank)

BEGIN

SET @Bank = CASE WHEN LEFT(@DBFetch,6) = 'SELECT' THEN '(' + @DBFetch + ')' ELSE @DBFetch END
SET @Bank = REPLACE(@Bank, CHAR(94),CHAR(39))
SET @Bank = REPLACE(@Bank,CHAR(45)+CHAR(45),CHAR(32))
SET @Bank = REPLACE(@Bank,CHAR(47)+CHAR(42),CHAR(32))

END

IF @DBWhere IS NOT NULL

BEGIN

SET @Cash = REPLACE(@DBWhere,'WHERE' ,CHAR(32))
SET @Cash = REPLACE(@Cash, CHAR(94),CHAR(39))
SET @Cash = REPLACE(@Cash,CHAR(45)+CHAR(45),CHAR(32))
SET @Cash = REPLACE(@Cash,CHAR(47)+CHAR(42),CHAR(32))

END

SET @DBField = REPLACE(@DBField,CHAR(32),CHAR(95))

SET @PCField = REPLACE(@PCField,CHAR(32),CHAR(95))

SET @PCBuild = REPLACE(@PCBuild,CHAR(32),CHAR(95))

SET @PCAdmin = REPLACE(@PCAdmin,CHAR(32),CHAR(95))

SET @DBTable = REPLACE(@DBTable,CHAR(32),CHAR(95))

SET @DBWrite = REPLACE(@DBWrite,CHAR(32),CHAR(95))

SET @DBWhere = CASE WHEN @DBWhere IS NULL THEN '' ELSE ' WHERE (' + @Cash + ') AND 0 = 0' END

SET @Some = ISNULL(@PCAdmin,'NA')

SET @Task = 'SELECT * INTO ' + @DBAE + ' FROM ' + @Bank + ' AS T WHERE 0 = 1'

--PRINT @Task

IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR

IF @Status = 0 SET @Status = @Return

IF @DBPivot IS NOT NULL

BEGIN

IF LEFT(@DBPivot,6) <> 'SELECT'

BEGIN

SET @Wink = 1

SET @Work = CHARINDEX('|',(@DBPivot)+'|')

WHILE @Work > 0

BEGIN

SET @Name = SUBSTRING(@DBPivot,@Wink,@Work-@Wink)

INSERT #DBAT (Name) VALUES (@Name)

SET @Wink = @Work + 1

SET @Work = CHARINDEX('|',(@DBPivot)+'|',@Wink)

END

END

ELSE

BEGIN

SET @Task = 'INSERT #DBAT (Name) ' + @DBPivot

SET @Task = REPLACE(@Task, CHAR(94),CHAR(39))
SET @Task = REPLACE(@Task,CHAR(45)+CHAR(45),CHAR(32))
SET @Task = REPLACE(@Task,CHAR(47)+CHAR(42),CHAR(32))

--PRINT @Task

IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR

IF @Status = 0 SET @Status = @Return

END

END

ELSE

BEGIN

SET @Task = ' INSERT #DBAT (Name)'
+ ' SELECT CONVERT(varchar(100),' + @DBField + ')'
+ ' FROM ' + @Bank + ' AS T ' + @DBWhere
+ ' GROUP BY CONVERT(varchar(100),' + @DBField + ')'
+ ' ORDER BY 1'

--PRINT @Task

IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR

IF @Status = 0 SET @Status = @Return

END

UPDATE #DBAT SET Name = @Some WHERE Name IS NULL

SET @DBAI = ''

SET @DBAO = ''

SET @Rich = ''

DECLARE Fields CURSOR FAST_FORWARD FOR
SELECT C.name
FROM tempdb.dbo.sysobjects AS O
JOIN tempdb.dbo.syscolumns AS C
ON C.id = O.id
AND C.name != @DBField
AND C.name != @PCField
AND O.name = @DBAE
ORDER BY C.colid

SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

OPEN Fields

SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

FETCH NEXT FROM Fields INTO @Same

SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

WHILE @@FETCH_STATUS = 0 AND @Status = 0

BEGIN

SET @DBAI = @DBAI + ', ' + @Same

FETCH NEXT FROM Fields INTO @Same

SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

END

CLOSE Fields DEALLOCATE Fields

SET @DBAI = SUBSTRING(@DBAI,3,4000)

DECLARE Fields CURSOR FAST_FORWARD FOR
SELECT Name
FROM #DBAT
ORDER BY Work

OPEN Fields

FETCH NEXT FROM Fields INTO @Same

WHILE @@FETCH_STATUS = 0 AND @Status = 0

BEGIN

IF LEN(@DBAO) < 7900 - LEN(@DBField) - LEN(@PCField) - LEN(@Same) - LEN(@Same)

BEGIN

SET @DBAO = @DBAO + ', ' + @PCBuild + '(CASE WHEN ISNULL(CONVERT(varchar(100),' + @DBField + '),'
+ CHAR(39) + @Some + CHAR(39) + ') = '
+ CHAR(39) + @Same + CHAR(39) + ' THEN '
+ @PCField + ' ELSE NULL END) AS '
+ CHAR(91) + @Same + CHAR(93)

END
ELSE
BEGIN

SET @Status = 50000

END

FETCH NEXT FROM Fields INTO @Same

END

CLOSE Fields DEALLOCATE Fields

IF @DBAdmin IN (1,3) SET @Rich = @Rich + ', ' + @PCBuild + '(' + @PCField + ') AS All_' + @PCBuild

IF @DBAdmin IN (2,3) SET @Rich = @Rich + ', COUNT(' + @PCField + ') AS All_COUNT'

IF @DBAdmin IN (2,3) SET @Rich = @Rich + ', MIN(' + @PCField + ') AS All_MIN'

IF @DBAdmin IN (2,3) SET @Rich = @Rich + ', MAX(' + @PCField + ') AS All_MAX'

SET ANSI_WARNINGS OFF

SET @Home = ''

SET @Name = ''

IF @DBTable IS NOT NULL

BEGIN

SET @Name = @DBTable

IF LEFT(@Name,2) = '##'

BEGIN

IF @DBUltra = 0

BEGIN

SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @Name + CHAR(39) + ') DROP TABLE ' + @Name

--PRINT @Task

IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR

IF @Status = 0 SET @Status = @Return

END

END

ELSE

BEGIN

IF @DBWrite IS NOT NULL SET @Home = @DBWrite + '.dbo.'

IF @DBUltra = 0

BEGIN

SET @Task = 'IF EXISTS (SELECT * FROM ' + @Home + 'sysobjects WHERE name = ' + CHAR(39) + @Name + CHAR(39) + ') DROP TABLE ' + @Home + @Name

--PRINT @Task

IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR

IF @Status = 0 SET @Status = @Return

END

END

END

IF @DBTable IS NOT NULL

BEGIN

IF @DBUltra = 0

BEGIN

IF @Status = 0 EXECUTE ( ' SELECT ' + @DBAI + @DBAO + @Rich
+ ' INTO ' + @Home + @Name
+ ' FROM ' + @Bank + ' AS T ' + @DBWhere
+ ' GROUP BY ' + @DBAI
+ ' ORDER BY ' + @DBAI ) SET @Return = @@ERROR

IF @Status = 0 SET @Status = @Return

END

ELSE

BEGIN

IF @Status = 0 EXECUTE ( ' INSERT ' + @Home + @Name
+ ' SELECT ' + @DBAI + @DBAO + @Rich
+ ' FROM ' + @Bank + ' AS T ' + @DBWhere
+ ' GROUP BY ' + @DBAI
+ ' ORDER BY ' + @DBAI ) SET @Return = @@ERROR

IF @Status = 0 SET @Status = @Return

END

END

ELSE

BEGIN

IF @Status = 0 EXECUTE ( ' SELECT ' + @DBAI + @DBAO + @Rich
+ ' FROM ' + @Bank + ' AS T ' + @DBWhere
+ ' GROUP BY ' + @DBAI
+ ' ORDER BY ' + @DBAI ) SET @Return = @@ERROR

IF @Status = 0 SET @Status = @Return

END

SET ANSI_WARNINGS ON

SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @DBAE + CHAR(39) + ') DROP TABLE ' + @DBAE

--PRINT @Task

EXECUTE (@Task)

DROP TABLE #DBAT

SET NOCOUNT OFF

RETURN (@Status)

--------------------------------

Reply to this Comment

This is academic as we got the data updated another way but were we're
wondering why something works the way it does:

Given these two SQL statements, we get the following results:

--This pulls 1 row
SELECT *
FROM CompletedRequirement cr
INNER JOIN vwRequirementList rl ON cr.RequirementID = rl.RequirementID
WHERE cr.CompletedRequirementID = 90271

--This updates 28000 rows
UPDATE cr
SET cr.CreatedBy = 'jsmith'
FROM CompletedRequirement cr
INNER JOIN vwRequirementList rl ON cr.RequirementID = rl.RequirementID
WHERE cr.CompletedRequirementID = 90271

The FROM, INNER JOIN, and WHERE clauses of both statements are the
same.

One CompletedRequirement entry can have many related entries in the
vwRequirementList view. Still, I would think that using the primary
key in a WHERE clause (CompletedRequirementID = 90271) would restrict
the UPDATE to 1 row as it does the SELECT.

Any ideas?

Thanks

Dennis

Reply to this Comment

In regards to the above, i think if has to do with joining on a view since i cannot reproduce it when joining on a table.

Reply to this Comment

Did you try moving the ID restriction to the inner join clause? That may help when dealing with the view.

Reply to this Comment

@Dennis,

Change the query to:

UPDATE CompletedRequirement
SET CompletedRequirement.CreatedBy = 'jsmith'
FROM CompletedRequirement cr
INNER JOIN vwRequirementList rl ON cr.RequirementID = rl.RequirementID
WHERE cr.CompletedRequirementID = 90271

Think of the from and join tables as separate from the update table and left side column of the set statement.

The updated table does need to be in the from/inner join clause as does need to have an alias.

I am assuming that the from/inner join clause is only there to by-pass a sub-query of "where CompletedRequirement.RequirementID is in (select all RequirementIDs from vwRequirementList)"; otherwise in this cause it would be superfluous as you are not using any values from the view.

Reply to this Comment

@Dennis,

Very interesting! I don't work with Views very much; but I would think that it would work as expected.

@Andrew,

I am pretty sure (but have not double-checked) that I have used an alias in join-based update statements... but I could be totally wrong.

Reply to this Comment

My goal in using the Update From syntax is to write select statements and then turn them into update statements without changing the code.

So i usually set them up like this:

UPDATE cr
SET cr.CreatedBy = 'jsmith'
--SELECT *
FROM CompletedRequirement cr
INNER JOIN vwRequirementList rl ON
cr.RequirementID = rl.RequirementID
WHERE cr.CompletedRequirementID = 90271

When I highlighted and ran the select section, i got 1 row. When i ran the whole statement i was very surprised that it affected 28080 rows. Luckily it was on a test system, but i would have felt comfortable running this in production after verifying the select results.

I got the below suggestion from another blog which works well and enables me to turn selects into updates:

WITH cte
AS
(
SELECT cr.*
FROM CompletedRequirement cr
INNER JOIN vwRequirementList rl ON cr.RequirementID = rl.RequirementID
WHERE cr.CompletedRequirementID = 90271
) UPDATE cte SET CreatedBy= 'jsmith'

Thanks for all your help,
Dennis

Reply to this Comment

@Dennis,

Thank you for following up on that one and sharing it with us. Only the blog log keeper knows how many but I suspect quite a few will stumble across this one and find it helpful.

Reply to this Comment

@Ben,

I believe it is required to use aliases in the join section of a join-based update query, however Dennis was attempting to update the alias. When updating via a join-based query in MS SQL (not sure if it is so for other RDBMS) you have to think of it as being referenced twice (like in a self-join query).

@Dennis,

Did you get the desired results from the query I posted previously?

Reply to this Comment

@Andrew,

Ah gotcha; it's been a while since I've use MS SQL (been rocking the groovy tunes of MySQL lately).

Reply to this Comment

This is for academic purpose. What i want to do is that there is table which has age and zip code. I am writing a code that takes four records through loop then copy them to temporary table (calculate average over there of the four records ) then store that avg value in a variable. After that want to replicate the avg value in the original table. I have done all the above steps only have problem in replicating the avg value only against the four record only that were first selected and for whom average was calculated.

DECLARE @count3 INT
SET @count3 = 0
WHILE (@count3 <@count2 )
BEGIN
INSERT INTO #MyTempTable VALUES (1)
SET @count3 = (@count3 + 1)
END
CREATE TABLE #MyTempTable (cola INT)
Select * from #MyTempTable
Delete #MyTempTable
Create table majeedwaheed(agee int, zipee int)
DECLARE cursor_first CURSOR FOR
SELECT Zip_code , age FROM Rt_md_rd_II
OPEN cursor_first
Declare @count5 INT
Set @count5 = 0

Declare @v_zip_code INT
Declare @v_age INT
Fetch NEXT from cursor_first into @v_zip_code,@v_age
While (@count5 < 4)
BEGIN
Select @v_zip_code as zip_code,@v_age as age insert into majeedwaheed values(@v_zip_code, @v_age)
Set @count5 = (@count5 + 1)
Fetch NEXT from cursor_first into @v_zip_code,@v_age
END
Declare @avg_age int

set @avg_age = (select avg(zipee) from majeedwaheed)

select @avg_age
Declare @count9 INT
Set @count9 = 4
While (@count9 < = 0)
begin
update Rt_md_rd_II set zip_code = @avg_age
set @count5 = (@count9 - 1)
END

the output of my code should be:

original table
Zip code Age
1305 21
1306 22
1309 23
1400 30

Output table
Zip code Age
1330 24
1330 24
1330 24
1300 24

Reply to this Comment

@Waheed, your post doesn't really have a question to it, though it sounds like you are attempting to ask one.

I am assuming that you want to find the average age of people in a zip code; is this correct? If so your query can be reduced to a single insert or update query.

@Ben, have you thought of creating a forum for your site and off-loading question based comments on articles into that?

Reply to this Comment

@Andrew,

Thank you Andrew for your reply, actually I could not clearly mention the problem. The problem was that the code that I pasted in my earlier post, was doing that (in total records of 12) it just picks first 4 records take average of the first 4 selected records and replicate the (zip code, age) with the record on whose values (zip code, age) computation was done. and then stopped. The code was not then picking the next four record, as it was supposed to pick.

I have now solved it. Thank you for proving such an informative portal and a platform for viewing and sharing different programing problems.
Thank you once again

Reply to this Comment

@Waheed, happy to help. I was curious about your problem as your code looked very verbose for what you seemed to be doing.

Looking at it, it seemed to me that you were attempting to calculate the averages one record at a time via cursors, instead of doing it all at once with a single update statement.

This may not be the case. However, I would recommend using cursors sparingly. SQL is optimised to do things in sets (groups) and cursors are resource hungry.

Reply to this Comment

@Andrew,

I hear what you're saying re:forum. The only time I ever get concerned is when people post large code snippets, especially since they do not format properly (and it's not really the appropriate place for it). But in general, I do like that the blog posts can lead to more indepth conversations.

Reply to this Comment

@Ben Nadel,

I can understand about the formatting frustrations, I hope that I have not left snippets unformatted :)

How about a hybrid of blog comments and forum? Have it when a blog is posted it automatically creates a forum thread.

Reply to this Comment

Truthfully, this is a great and helpful post from you. I've needed to do such a thing and I did not realize how to achieve it until I found yours.

Reply to this Comment

that alias concept was what I was looking for. I might actually like sql some day. thanks.

Reply to this Comment

UPDATE T SET T.AccesAgreementId = A.Id FROM TemporaryHousing T INNER JOIN AccessAgreements A ON A.RENumber = T.RENumber

UPDATE T SET T.AccesAgreementId = A.Id FROM Remediation T INNER JOIN AccessAgreements A ON A.RENumber = T.RENumber

Reply to this Comment

Hi all, hopefully it's okay to post this here. Seems like someone here (hopefully Ben) would know how to help me. I am trying to do the same thing you have done with this join and update, just a little different. Here goes:

I am not sure the syntax is correct. This is probably the most advanced SQL query I have attempted.

UPDATE File
Set ShortFileName = File2.ShortFileName
From File
Inner Join File2
ON File.MatterID = File2.MatterID

It's important that the query is smart enough to match the rows on each table, find the match and return the ShortFileName from that row only, updating the real File table with the data from the new File2 table (which I am importing simply to be able to do the lookup using the Join statement).

Reply to this Comment

@Jashua,

That looks roughly correct; the UPDATE-JOIN syntax changes with each database, so you might have to tweak it depending on your environment. Are you getting an error when you run this?

Reply to this Comment

Need help:
UPDATE Test
SET Debt = Debt + SalesRecord.Debt
WHERE EXISTS
(select *
from SalesRecord
where Test.Customer_Name = SalesRecord.Customer_Name AND Test.Debt>2000);

Error:The multi-part identifier "SalesRecord.Debt" could not be bound.

Reply to this Comment

Thanks Ben

Your post helped an amateur make an update where I had to join two tables. Normally I don't do joins when I have to update, so i googled and ended at your blog, where I found the formula.

So thanks a lot, and have a great day. Yours is starting (if you are a worm in the apple), and my workday is ending in a couple of hours.

Greetings from Valby, Denmark

Reply to this Comment

@Justin,

I don't think that the tables in the EXISTS clause can be referenced externally. Plus, I am not sure there is any guarantee as to which record comes back in the EXISTS. The clause dictates that it will return TRUE if even one record matches (at which point it stops searching). It sounds like you need to use something other than EXISTS.

@Michael,

Very awesome - glad to help. I also don't JOIN all that often in an UPDATE statement; but, when you need to, this is super powerful.

Reply to this Comment

@Ben

It worked! Thanks for responding, I'm just now seeing it as gmail caught the notifications as spam, and they never made it to MS Outlook.

Thanks again!

Reply to this Comment

This failes because the command requires update permission on all the fields used in the select - not only is_stud.
UPDATE b SET b.is_stud = 1...

Reply to this Comment

Thanks, Ben,

Your site is superb - better than the original documentation - when it comes to just making an SQL query work, esp with ColdFusion.

How do we figure out your schedule so we can go to the right conference and get our picture taken with you?

Cheers from Bloomington, Indiana

Reply to this Comment

@Willy,

I have not yet dealt with a database that only allows update on certain fields. I can imagine that complicates many things.

@John,

Ha ha ha, thanks :) I try to go to as many ColdFusion conferences as I can afford / allot time for. Some years that's more than others. Will hopefully be getting to cf.Objective() this year. I did go to BFusion/BFLex the last 2 years - seems like the one that would be closest to you :)

Reply to this Comment

Hi,

You look well built, how do you work out and be a programmer at the same time?

Tien

Reply to this Comment

@Tien,

Ha ha, I don't do much else other than programming and work out :D Though, I haven't worked out much lately. Trying to get back on track.

@Richard, @Tokhir,

Ha ha, I'm just trying to keep my JOINs easier :P

Reply to this Comment

What about the order of updates? Will this always be a valid statement?

update u set
u.UserName = t.UserName,
u.updated = case when u.UserName <> t.UserName then 1 else 0 end
from Users u
join UserImport t on u.ID = t.ID

Reply to this Comment

Hi there - I have been struggling with the syntax for an update on a select with a foxpro databsase. This blog has a lot of good ideas, but I think I have tried every one of them without success.

I am entering my SQL into a text box and then doing a database .execute
Set dbCompany = OpenDatabase(strDatabase, dbDriverComplete, False, "FoxPro 2.6")
dbCompany.Execute (ExportSQL)

The ones that run and do not give syntax errors are as follows, but the update more records than they should. Any help appreciated.

UPDATE Trade set zComments = "Updated" where Qtyord in (Select QtyOrd from ((Trans ts inner join Trade td ON ts.Internref = td.Intordref) inner join Inventry inv ON td.Accountid = inv.Uniqueid) where Trantype = 39 AND QtyOrd > QtyDel and inv.Accountno = "Test")

UPDATE Trade set zComments = "Updated" where Qtyord in (Select QtyOrd from Trans ts, Trade td where ts.Internref = td.Intordref and ts.Trantype = 39 AND td.QtyOrd < td.QtyDel and td.ledgerno = 1)

THIS ONE GIVES ERROR - CANNOT EXECUTE A SELECT QUERY
*************************************************
WITH Trade
AS
(SELECT td.*
FROM Trade td
INNER JOIN Trans ts ON td.intordref = ts.internref
WHERE ts.trantype = 39
) UPDATE Trade SET zComments = 'Updated'

THIS ONE GIVES ERROR - SYNTAX
*******************************
HOTHOUSEUPDATE Trade SET zcomments = "Updated" FROM (Trade td inner join Trans ts ON Td.Intordref = ts.Internref where ts.Trantype = 39 AND td.QtyOrd > td.QtyDel and td.Ledgerno = 1)

Going quietly nuts here :o)

Reply to this Comment

Hi there - I have been struggling with the syntax for an update on a select with a foxpro databsase. This blog has a lot of good ideas, but I think I have tried every one of them without success.

I am entering my SQL into a text box and then doing a database .execute
Set dbCompany = OpenDatabase(strDatabase, dbDriverComplete, False, "FoxPro 2.6")
dbCompany.Execute (ExportSQL)

The ones that run and do not give syntax errors are as follows, but the update more records than they should. Any help appreciated.

UPDATE Trade set zComments = "Updated" where Qtyord in (Select QtyOrd from ((Trans ts inner join Trade td ON ts.Internref = td.Intordref) inner join Inventry inv ON td.Accountid = inv.Uniqueid) where Trantype = 39 AND QtyOrd > QtyDel and inv.Accountno = "Test")

UPDATE Trade set zComments = "Updated" where Qtyord in (Select QtyOrd from Trans ts, Trade td where ts.Internref = td.Intordref and ts.Trantype = 39 AND td.QtyOrd < td.QtyDel and td.ledgerno = 1)

THIS ONE GIVES ERROR - CANNOT EXECUTE A SELECT QUERY
*************************************************
WITH Trade
AS
(SELECT td.*
FROM Trade td
INNER JOIN Trans ts ON td.intordref = ts.internref
WHERE ts.trantype = 39
) UPDATE Trade SET zComments = 'Updated'

THIS ONE GIVES ERROR - SYNTAX
*******************************
UPDATE Trade SET zcomments = "Updated" FROM (Trade td inner join Trans ts ON Td.Intordref = ts.Internref where ts.Trantype = 39 AND td.QtyOrd > td.QtyDel and td.Ledgerno = 1)

Going quietly nuts here :o)

Reply to this Comment

Hi Ben,
I have following to 2 tables

SQL> select * from a;

EN SAL
-- ----------
a 100
b 200
c 300
d 400
e 500

SQL> select * from b;

EN SAL
-- ----------
aa
bb
cc
dd
ee

I want to update SAL in table b corresponding to table a.
please suggest me.

Reply to this Comment

Thanks! I've found this article helpful a few times. Good information and well indexed with google.

Reply to this Comment

How many times have I come back to this post trying to figure out the syntax for update joins?

Good man!

Reply to this Comment

hmm..,
why it doesn't work on my code??
i'm doing this on sybase (it suposed to be using the same t-sql with sql server right?)

it keep giving me : "exist_on_psak_ecf_file" does not found, i can assure that field does exist

here's the snippet of it :

UPDATE aa
SET aa.exist_on_psak_ecf_file = 'Y'
FROM #Active_Account aa
INNER JOIN DBA.PSAK_ECF_FILE EF
ON aa.masterID = EF.acc_mstr_id
WHERE aa.having_ecf = 'Y'

but it work with this kind of code:
UPDATE #Active_Account
SET aa.exist_on_psak_ecf_file = 'Y'
FROM #Active_Account aa
INNER JOIN DBA.PSAK_ECF_FILE EF
ON aa.masterID = EF.acc_mstr_id
WHERE aa.having_ecf = 'Y'

would appreciate any help on this..,

thanks in advance.. :D

Reply to this Comment

found my solution,
the right query is one that's work (obviuosly)

well why is that ?
after reading the post again and seeing ben's code and pinal's code i found that pinal example is one that's work :

DELETE tableName
FROM tableName tn
INNER JOIN JoinedTable jn ON jn.col = tn.col
WHERE jn = someval

so youcan only use aliases in join filter or where clause but not after delete/update word

Reply to this Comment

I just stumbled across this whilst looking for something else and I am so impressed that you can use a JOIN in a delete.

Thanks Ben and Pinal!

SQL GETS HUGE IN A HURRY!

Reply to this Comment

I am not a programmer but I have this SQL subject this session and have to prepare for it. What all topics should be covered in it?
And has anyone studied from this course www.wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance...
would really appreciate help

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.