A CASE expression returns a value from the THEN portion of the clause. You could use it thusly: SELECT * FROM sys.indexes i JOIN sys.partitions p ON i.index_id = p.index_id JOIN sys.allocation_units a ON CASE WHEN a.type IN (1, 3) AND a.container_id = p.hobt_id THEN 1 WHEN a.type IN (2) AND a.container_id = p.partition_id THEN … Read more
Zero’s answer is basically a reduce operation. If I had more than a handful of dataframes, I’d put them in a list like this (generated via list comprehensions or loops or whatnot): dfs = [df0, df1, df2, …, dfN] Assuming they have a common column, like name in your example, I’d do the following: import … Read more
I find that if you’re familiar with SQL syntax, using the LINQ query syntax is much clearer, more natural, and makes it easier to spot errors: var id = 1; var query = from post in database.Posts join meta in database.Post_Metas on post.ID equals meta.Post_ID where post.ID == id select new { Post = post, … Read more
Here’s an awk option, so you can avoid the bash dependency (for portability): $ awk -F’|’ ‘NR==FNR{check[$0];next} $2 in check’ file2 file1 How does this work? -F’|’ — sets the field separator ‘NR==FNR{check[$0];next} — if the total record number matches the file record number (i.e. we’re reading the first file provided), then we populate an … Read more
It goes something like: from t1 in db.Table1 join t2 in db.Table2 on t1.field equals t2.field select new { t1.field2, t2.field3} It would be nice to have sensible names and fields for your tables for a better example. 🙂 Update I think for your query this might be more appropriate: var dealercontacts = from contact … Read more
The two queries are the same, except the second is ANSI-92 SQL syntax and the first is the older SQL syntax which didn’t incorporate the join clause. They should produce exactly the same internal query plan, although you may like to check. You should use the ANSI-92 syntax for several of reasons The use of … Read more
There is no difference in performance. However, the first style is ANSI-89 and will get your legs broken in some shops. Including mine. The second style is ANSI-92 and is much clearer. Examples: Which is the JOIN, which is the filter? FROM T1,T2,T3…. WHERE T1.ID = T2.ID AND T1.foo = ‘bar’ AND T2.fish = 42 … Read more
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
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;
They are not the same thing. Consider these queries: SELECT * FROM Orders LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID WHERE Orders.ID = 12345 and SELECT * FROM Orders LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID AND Orders.ID = 12345 The first will return an order and its lines, if any, for order number 12345. The second will return … Read more