SQLAuthority News – Interesting Read – Using A SQL JOIN In A SQL UPDATE/Delete Statement – Ben Nadel

As everybody know SQL is what I like most. Before I was into SQL Server, I was very much into ColdFusion. ColdFusion is still my most favorite programming language. I still program in ColdFusion, infect my personal website http://www.pinaldave.com is in ColdFusion. I regularly read ColdFusion blog and latest updates in ColdFusion. Recently at company where I work, we upgraded to ColdFusion 8 and .NET 2.0 (C# is our preferred language in .NET technology). Both of this languags work with SQL Server 2005 very well in my company.

My favorite blog for ColdFusion technology is blog of BEN NADEL . Ben writes excellent articles in ColdFusion and have absolutely control over his technical thought process – a true genius. Recently he wrote two articles about how SQL JOINS can be used in UPDATE and DELETE queries. When I read them I felt I should have wrote similar articles for this blog.

Please visit both the articles to get the proper understanding how JOIN can be used. The code is SQL with great amount of explanation.

Using A SQL JOIN In A SQL UPDATE Statement
This example is explained with UPDATE and INNER JOIN.

Using A SQL JOIN In A SQL DELETE Statement
This example is explained with DELETE and LEFT OUTER JOIN. Very good example.

If you on the site of Ben Nadel, checkout the Free ColdFusion Calender System.

Let me know what you think about these articles.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

6 thoughts on “SQLAuthority News – Interesting Read – Using A SQL JOIN In A SQL UPDATE/Delete Statement – Ben Nadel

  1. It’s a really good tip about update. I am wondering if we need to join the same table several times. will the aliases work on the update query? If you have the answer, could you please email it to me. I am getting stuck. Thanks.

    Like

  2. Hello Dave,

    I have a dynamic sql statement that creates

    DELETE FROM [dbo].[Indexes] I INNER JOIN [dbo].[Tables] T ON [I].[TableID_FK] = [T].[TableID_PK] WHERE [T].[DatabaseID_FK]= 308

    The code that created ths statement is:
    SET @MYSQL = ‘DELETE FROM ‘ + @TABLENAME + @MYJOINCONDITION + ‘ WHERE ‘ + @TABLE_FK + ‘= ‘ + CAST(@DBID AS NVARCHAR)

    The error SQL is returning is: Incorrect syntax near ‘I’.

    Maybe it’s just late and my eyes are tired, but I’m having trouble seing where the error is. Of course, you can tell that I’m using a join inside the delete statement.

    I’m looking to delete all records from the index table that reference a table that is inside a database that is also being deleted.

    As this script continues to attempt to delete the database this error is returned: Msg 547, Level 16, State 0, Line 212
    The DELETE statement conflicted with the REFERENCE constraint “FK_Databases_SQLServer”. The conflict occurred in database “MyTestDatabase”, table “dbo.Databases”, column ‘DBServerID_FK’.

    Thanks for any time spent in reviewing this design.

    Dale

    Like

  3. Hi Dale,

    try this: SET @MYSQL = ‘DELETE ‘ + @TABLENAME + ‘ FROM ‘ + @TABLENAME + @MYJOINCONDITION + ‘ WHERE ‘ + @TABLE_FK + ‘= ‘ + CAST(@DBID AS NVARCHAR)

    Thanks,

    Tejas

    Like

  4. Hi ,

    I want to update a table based on distinct data combination of 3 columns. Let me explain .

    Columns in the table

    ENQUIRY_NO,MODIFY_BY ,ENQ_DATE ,MAIN_STATUS ,LINE_STATUS

    Now i want to update the MAIN_STATUS of the table based on COMBINATION OF DISTINCT ENQRUIY_NO,MODIFY_BY,ENQ_DATE and with HARDCODED condition of LINE_STATUS.

    If even a Single LINE_STATUS is in “Order” make the MAIN_STATUS as “Order” for all the lines having same (ENQUIRY_NO,MODIFY_BY ,ENQ_DATE)

    and after that next step is —
    If even a Single LINE_STATUS is in “Working” make the MAIN_STATUS as “Working” for all the lines having same (ENQUIRY_NO,MODIFY_BY ,ENQ_DATE)

    basically i want to achive it with 2 simple t-SQL UPDATE statements and not through lenghty procedure…..

    Could you please help me….

    Any help would be highly appreciated

    Thanks,
    Jignesh

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s