UPDATE im SET mf_item_number = gm.SKU –etc FROM item_master im JOIN group_master gm ON im.sku = gm.sku JOIN Manufacturer_Master mm ON gm.ManufacturerID = mm.ManufacturerID WHERE im.mf_item_number like ‘STA%’ AND gm.manufacturerID = 34 To make it clear… The UPDATE clause can refer to an table alias specified in the FROM clause. So im in this case … Read more
Try DISABLE KEYS or SET FOREIGN_KEY_CHECKS=0; Make sure to SET FOREIGN_KEY_CHECKS=1; after.
To your remark in the comments to your question: “…SavingChanges (for each record)…” That’s the worst thing you can do! Calling SaveChanges() for each record slows bulk inserts extremely down. I would do a few simple tests which will very likely improve the performance: Call SaveChanges() once after ALL records. Call SaveChanges() after for example … Read more
Use the toSql() method on a QueryBuilder instance. DB::table(‘users’)->toSql() would return: select * from `users` This is easier than wiring up an event listener, and also lets you check what the query will actually look like at any point while you’re building it. Note: This method works for query builder or Eloquent, however toSql() is … Read more
You don’t have full joins in MySQL, but you can sure emulate them. For a code sample transcribed from this Stack Overflow question you have: With two tables t1, t2: SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id UNION SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id The query above … Read more
ORDER BY column1 DESC, column2 This sorts everything by column1 (descending) first, and then by column2 (ascending, which is the default) whenever the column1 fields for two or more rows are equal.
There is no difference. Reason: Books on-line says “COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )“ “1” is a non-null expression: so it’s the same as COUNT(*). The optimizer recognizes it for what it is: trivial. The same as EXISTS (SELECT * … or EXISTS (SELECT 1 … … Read more
The MSDN documentation for datetime recommends using datetime2. Here is their recommendation: Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications. datetime2 has larger … Read more
Most databases support != (popular programming languages) and <> (ANSI). Databases that support both != and <>: MySQL 5.1: != and <> PostgreSQL 8.3: != and <> SQLite: != and <> Oracle 10g: != and <> Microsoft SQL Server 2000/2005/2008/2012/2016: != and <> IBM Informix Dynamic Server 10: != and <> InterBase/Firebird: != and <> … Read more
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