Hot answers tagged

19

This is documented in UPDATE (Transact-SQL): SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column. In your code example, sum is the (unwise) name of a column, not an aggregate. db<>fiddle demo


12

It's documented with the SELECT statement and it's called "TABLE Command" there. In the SQL standard it's called an "explicit table": The <explicit table>    TABLE <table or query name> is equivalent to the    ( SELECT * FROM <table or query name> ) This seems to be part of the standard at least ...


9

The msdb.dbo.sp_send_dbmail procedure (or other procedures) only accept either a parameter or a literal value. Consider changing the execution of sp_send_dbmail to this: SET @usage_data += ' end of data' exec msdb.dbo.sp_send_dbmail @profile_name='some_mail_profile', @recipients=@admin_list, @subject='Mail from sql server', @body=@usage_data, @importance=...


7

The LEFT() function in your example is not part of the EXEC statement. It is inside the string representing part of the dynamic SQL to be executed by your EXEC statement. In contrast, the CAST() function is used to build that string. If you use the correct quotation marks – ' instead of ‘ & ’ – then perhaps you can also see the difference in the syntax ...


4

FROM is - logically - evaluated before SELECT, so despite the textual order of your query, ta is known to SUM. There are however a number of things in your query that won't work: You need a FROM clause A JOIN is performed between 2 tables You can probably rewrite your query to: SELECT table.id , SUM(10/ta.value) AS amount FROM tableA ta WHERE ... ...


3

If you want to generate JSON, it is easier to use JSON functions for that, e.g.: string_agg(distinct jsonb_build_object('name', param_name, 'value', value)::text, ',') Or create a proper JSON array of the result: jsonb_agg(distinct jsonb_build_object('name', param_name, 'value', value)) Online example: https://rextester.com/EPOX97933


3

The CASE expression doesn't have the END CASE W.T1_SymbolType WHEN 'terminal' THEN convert (int, SUBSTRING(W.T1_DeviceTag, PATINDEX('%[:]%', W.T1_DeviceTag)+1, (LEN(W.T1_DeviceTag))-(PATINDEX('%[:]%', W.T1_DeviceTag)))) END ) Your next problem will be that the ORDER BY is invalid because it is not at the top level (or part of a ...


2

You can try this. @Stringss is a variable to simulate the field you already obtained DECLARE @String NVARCHAR(MAX)='S:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\tempdb.mdf' SELECT REVERSE(SUBSTRING(REVERSE(@String),0,CHARINDEX('\',REVERSE(@String),0)))


2

Yes, it is syntactically correct. The ON clause can be any boolean expression - i.e. as long as it results in a boolean value (TRUE, FALSE, UNKNOWN). Note that you have 3 tables that are visible in that ON clause: employee, department and payroll. You'll get an error if payroll_type is a column in more than one of them.


2

The (2, 3) has type record, i.e. Postgres doesn't know which (or even how many) columns it contains. You can use the .* syntax only with known ("registered") types. You could define your own tuple type and use that instead of a record: CREATE TYPE tuple AS (a int, b int); SELECT 1, (CASE WHEN TRUE THEN '(2, 3)'::tuple ELSE '(4, 5)' END).*; SELECT 1, (CASE ...


2

if we were to update student relation wouldn't this be rejected This is correct. If there is no ON UPDATE action defined in the foreign key reference, your update of referenced key would coflict with the foreign key constaint so it would be rejected.


1

Lennart's answer is correct. To make things a little clearer for you I'll ask that you change your definition of 'before' from this.. SWEATY (SELECT) FEET (FROM, with JOINs) WILL (WHERE) GIVE (GROUP BY) HORRIBLE (HAVING) ODORS (ORDER BY) (Good luck getting that out of your head) ... to follow the Query Order of Execution (link) which is how the query is ...


1

If you need to use a full outer join, use a full outer join. It exists for a reason. In general, though, it should be an uncommon tool in the toolbox. When designing systems, you generally try to minimize the number of outer joins that you need to do. In this case, it would seem to make more sense to have a general transaction table that both ...


1

As mentioned in the comments, your original problem is the unnecessary semicolon ( ; ). Code Review By building the query the way you did, you are introducing SQL Injection Vulnerability. (obligatory XKCD reference). To prevent SQL Injections, use BIND variables. Most RDBMS allow you to use ? as a place holder. Oracle supports this and Named place ...


1

Try using ROW_NUMBER windowing function as in this example: --demo setup drop table if exists Table1 go CREATE TABLE Table1 ( ID_NUM INTEGER, PHASE VARCHAR(6), DATE DATE, NOTE VARCHAR(12) ); --solution INSERT INTO Table1 (ID_NUM, PHASE, DATE, NOTE) VALUES ('30329', 'Phase1', '1-1-20', 'example note'), ('30329', 'null', '1-1-20', 'example note'...


1

You'll need to do it in several steps: Add the column to the store (I'm not sure what the type is, maybe it's something else): ALTER TABLE store ADD COLUMN location VARCHAR(50) Populate it, based on the most prevalent location of the employees. I'm not entirely familiar with MySQL syntax but the query should look something like this: UPDATE store SET ...


1

In the first example, you end up with 1 + 2 IS NOT NULL - which, it is not, 3 is certainly not null. 3 IS NOT NULL evaluates to true, casting it to an int makes it 1 again. Hopefully this makes it clearer: testdb=# select 1 is not null; ?column? ---------- t (1 row) testdb=# select 1 is not null::int; int4 ------ 1 (1 row) testdb=# select 1 is ...


1

What you're looking for is the SYSUTCDATETIME function, which is used like this: SELECT SYSUTCDATETIME(); It returns the current system date and time, adjusted to UTC time. This seems similar to what the postgres equivalent does (except it looks like current_timestamp includes timezone information, while SYSUTCDATETIME does not). If you need the time ...


1

As the users in the comments above pointed me in the right direction, the issue was a circular reference error between the name of the created field (TextBox) and the Control Source. These can be viewed in the Property Sheet of a form or report under All tab. Obviously you don't want to rename the Control Source between this references the table or query the ...


1

Generic code for calculating 1,2,3,4,..6,...12 years / quarters / months / weeks / days /hours moving average, median, percentiles, etc. summary stats where table contains a list of individual time records (like sales transactions,etc) WITH grid AS ( SELECT end_time, start_time FROM ( SELECT end_time , lag(end_time, 12, '...


1

to escape a ' in a string in postgres, you have to double it List of escape sequences for Postgresql string data types so this give you this : select string_agg(distinct '{name:''' || name || ''',value:''' || value || '''}',',') as "FIELD" I tested it, it works =) However, i don't know why you want to create this field, but it's probably better to ...


1

Presumably your trigger firing on UPDATE executes an UPDATE on the same table, which itself fires the triggers, and so on recursively until the stack is full and the stack depth limit exceeded error is raised. Here's a bare bones example demonstrating the problem: create table tst(id int primary key, col int, trigger boolean); create function trg() ...


Only top voted, non community-wiki answers of a minimum length are eligible