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.
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)
6 Comments. Leave new
Pinal Dave, thank you very much for the kind words and the most excellent SQL advice :)
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.
thanked post
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
Hi Dale,
try this: SET @MYSQL = ‘DELETE ‘ + @TABLENAME + ‘ FROM ‘ + @TABLENAME + @MYJOINCONDITION + ‘ WHERE ‘ + @TABLE_FK + ‘= ‘ + CAST(@DBID AS NVARCHAR)
Thanks,
Tejas
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