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 https://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.

Solarwinds

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 (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database
Next Post
SQL SERVER – SQL Joke, SQL Humor, SQL Laugh – Valid SQL Error

Related Posts

6 Comments. Leave new

  • Pinal Dave, thank you very much for the kind words and the most excellent SQL advice :)

    Reply
  • 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.

    Reply
  • thanked post

    Reply
  • 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

    Reply
  • Hi Dale,

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

    Thanks,

    Tejas

    Reply
  • Jignesh Shah
    May 28, 2010 10:43 am

    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

    Reply

Leave a Reply

Menu