You don’t quite have SQL Server’s proprietary UPDATE FROM syntax down. Also not sure why you needed to join on the CommonField and also filter on it afterward. Try this: UPDATE t1 SET t1.CalculatedColumn = t2.[Calculated Column] FROM dbo.Table1 AS t1 INNER JOIN dbo.Table2 AS t2 ON t1.CommonField = t2.[Common Field] WHERE t1.BatchNo = ‘110’; … Read more
You can obtain this information and much, much more by querying the Information Schema views. This sample query: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N’Customers’ Can be made over all these DB objects: CHECK_CONSTRAINTS COLUMN_DOMAIN_USAGE COLUMN_PRIVILEGES COLUMNS CONSTRAINT_COLUMN_USAGE CONSTRAINT_TABLE_USAGE DOMAIN_CONSTRAINTS DOMAINS KEY_COLUMN_USAGE PARAMETERS REFERENTIAL_CONSTRAINTS ROUTINES ROUTINE_COLUMNS SCHEMATA TABLE_CONSTRAINTS TABLE_PRIVILEGES TABLES VIEW_COLUMN_USAGE VIEW_TABLE_USAGE VIEWS
Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e., no INSERT or UPDATE statements allowed). A function can be used inline in SQL statements if it returns a scalar value or can be joined upon if it returns a result set. A point worth noting from comments, which summarize the … Read more
You can try it this way: /* Get the data into a temp table */ SELECT * INTO #TempTable FROM YourTable /* Drop the columns that are not needed */ ALTER TABLE #TempTable DROP COLUMN ColumnToDrop /* Get results and drop temp table */ SELECT * FROM #TempTable DROP TABLE #TempTable
Escape the square brackets: … WHERE m.definition Like ‘%\[ABD\]%’ ESCAPE ‘\’ Then the square brackets will be treated as a string literals not as wild cards.
SQL Server 2000, 2005, 2008, 2012, 2014, 2016, 2017 or 2019: SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE’ To show only tables from a particular database SELECT TABLE_NAME FROM [<DATABASE_NAME>].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ Or, SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ AND TABLE_CATALOG=’dbName’ –(for MySql, use: TABLE_SCHEMA=’dbName’ ) PS: For SQL … Read more
Can anyone give me a good example of when CROSS APPLY makes a difference in those cases where INNER JOIN will work as well? See the article in my blog for detailed performance comparison: INNER JOIN vs. CROSS APPLY CROSS APPLY works better on things that have no simple JOIN condition. This one selects 3 … Read more
I believe an UPDATE FROM with a JOIN will help: MS SQL UPDATE Sales_Import SET Sales_Import.AccountNumber = RAN.AccountNumber FROM Sales_Import SI INNER JOIN RetrieveAccountNumber RAN ON SI.LeadID = RAN.LeadID; MySQL and MariaDB UPDATE Sales_Import SI, RetrieveAccountNumber RAN SET SI.AccountNumber = RAN.AccountNumber WHERE SI.LeadID = RAN.LeadID;
Single quotes are escaped by doubling them up, just as you’ve shown us in your example. The following SQL illustrates this functionality. I tested it on SQL Server 2008: DECLARE @my_table TABLE ( [value] VARCHAR(200) ) INSERT INTO @my_table VALUES (‘hi, my name”s tim.’) SELECT * FROM @my_table Results value ================== hi, my name’s tim.
With a clustered index the rows are stored physically on the disk in the same order as the index. Therefore, there can be only one clustered index. With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indices, although each new index … Read more