How to select multiple rows filled with constants?
SELECT 1, 2, 3 UNION ALL SELECT 4, 5, 6 UNION ALL SELECT 7, 8, 9
sql
SQL query for finding records where count > 1
Use the HAVING clause and GROUP By the fields that make the row unique The below will find all users that have more than one payment per day with the same account number SELECT user_id , COUNT(*) count FROM PAYMENT GROUP BY account, user_id , date HAVING COUNT(*) > 1 Update If you want to … Read more
MySQL CONCAT returns NULL if any field contain NULL
convert the NULL values with empty string by wrapping it in COALESCE SELECT CONCAT(COALESCE(`affiliate_name`,”),’-‘,COALESCE(`model`,”),’-‘,COALESCE(`ip`,”),’-‘,COALESCE(`os_type`,”),’-‘,COALESCE(`os_version`,”)) AS device_name FROM devices
How to update only one field using Entity Framework?
Ladislav’s answer updated to use DbContext (introduced in EF 4.1): public void ChangePassword(int userId, string password) { var user = new User() { Id = userId, Password = password }; using (var db = new MyEfContextName()) { db.Users.Attach(user); db.Entry(user).Property(x => x.Password).IsModified = true; db.SaveChanges(); } }
Is SQL syntax case sensitive?
The SQL keywords are case insensitive (SELECT, FROM, WHERE, etc), but they are often written in all caps. However, in some setups, table and column names are case sensitive. MySQL has a configuration option to enable/disable it. Usually case sensitive table and column names are the default on Linux MySQL and case insensitive used to … Read more
SQL Server – transactions roll back on error?
You can put set xact_abort on before your transaction to make sure sql rolls back automatically in case of error.
How do I execute a stored procedure once for each row returned by query?
use a cursor ADDENDUM: [MS SQL cursor example] declare @field1 int declare @field2 int declare cur CURSOR LOCAL for select field1, field2 from sometable where someotherfield is null open cur fetch next from cur into @field1, @field2 while @@FETCH_STATUS = 0 BEGIN –execute your sproc on each row exec uspYourSproc @field1, @field2 fetch next from … Read more
Change Schema Name Of Table In SQL
Create Schema : IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name=”exe”)) BEGIN EXEC (‘CREATE SCHEMA [exe] AUTHORIZATION [dbo]’) END ALTER Schema : ALTER SCHEMA exe TRANSFER dbo.Employees
Count the number of occurrences of a string in a VARCHAR field?
This should do the trick: SELECT title, description, ROUND ( ( LENGTH(description) – LENGTH( REPLACE ( description, “value”, “”) ) ) / LENGTH(“value”) ) AS count FROM <table>
Alter a MySQL column to be AUTO_INCREMENT
ALTER TABLE document MODIFY COLUMN document_id INT auto_increment