For your specific query, you can do: UPDATE purchaseOrder SET purchaseOrder_status=”COMPLETED” WHERE purchaseOrder_ID = ‘@purchaseOrder_ID’ and not exists (SELECT * FROM itemsOrdered WHERE purchaseOrder_ID = ‘@purchaseOrdered_ID’ AND status=”PENDING” ) However, I might guess that you are looping at a higher level. To set all such values, try this: UPDATE purchaseOrder SET purchaseOrder_status=”COMPLETED” WHERE not exists … Read more
And has precedence over Or, so, even if a <=> a1 Or a2 Where a And b is not the same as Where a1 Or a2 And b, because that would be Executed as Where a1 Or (a2 And b) and what you want, to make them the same, is the following (using parentheses to … Read more
ALTER TABLE provider ADD PRIMARY KEY(person,place,thing); If a primary key already exists then you want to do this ALTER TABLE provider DROP PRIMARY KEY, ADD PRIMARY KEY(person, place, thing);
The where clause is filtering away rows where the left join doesn’t succeed. Move it to the join: SELECT `settings`.*, `character_settings`.`value` FROM `settings` LEFT JOIN `character_settings` ON `character_settings`.`setting_id` = `settings`.`id` AND `character_settings`.`character_id` = ‘1’
I use schema and data comparison functionality built into the latest version Microsoft Visual Studio 2015 Community Edition (Free) or Professional / Premium / Ultimate edition. Works like a charm! http://channel9.msdn.com/Events/Visual-Studio/Launch-2013/VS108 Red-Gate’s SQL data comparison tool is my second alternative: (source: spaanjaars.com) http://www.red-gate.com/products/sql-development/sql-compare/ http://www.red-gate.com/products/sql-development/sql-data-compare/
getdate() or getutcdate().
[Since this question will likely be closed as a duplicate.] If SQL Server hasn’t been restarted (and the plan hasn’t been evicted, etc.), you may be able to find the query in the plan cache. SELECT t.[text] FROM sys.dm_exec_cached_plans AS p CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t WHERE t.[text] LIKE N’%something unique about your query%’; If … Read more
You could try some sort of COALESCE trick, eg: SELECT COALESCE( CASE WHEN condition1 THEN calculation1 ELSE NULL END, CASE WHEN condition2 THEN calculation2 ELSE NULL END, etc… )
— replace NVARCHAR(42) with the actual type of your column ALTER TABLE your_table ALTER COLUMN your_column NVARCHAR(42) NULL
The idea is very simple – the query and the data are sent to the database server separately. That’s all. The root of the SQL injection problem is in the mixing of the code and the data. In fact, our SQL query is a legitimate program. And we are creating such a program dynamically, adding … Read more