DELETE FROM `table` AS `alias` … WHERE `alias`.`column` … why syntax error?
What @Matus and @CeesTimmerman said about MSSQL, works in MySQL 5.1.73 too: delete <alias> from <table> <alias> where <alias>.<field>…
sql
Why do we need GROUP BY with AGGREGATE FUNCTIONS?
It might be easier if you think of GROUP BY as “for each” for the sake of explanation. The query below: SELECT empid, SUM (MonthlySalary) FROM Employee GROUP BY EmpID is saying: “Give me the sum of MonthlySalary’s for each empid” So if your table looked like this: +—–+————+ |empid|MontlySalary| +—–+————+ |1 |200 | +—–+————+ … Read more
Select query to get data from SQL Server
SqlCommand.ExecuteNonQuery Method You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements. Although the ExecuteNonQuery returns no rows, any output parameters or … Read more
MySQL: Count the distinct rows per day
SELECT DATE(timestamp) Date, COUNT(DISTINCT ipNum) totalCOunt FROM tableName GROUP BY DATE(timestamp) SQLFiddle Demo
How to write Count Query In jOOQ
The most straight-forward way to implement what you’re requesting is this, by using selectCount(): int count = DSL.using(configuration) .selectCount() .from(Table) .fetchOne(0, int.class); Alternatively, you can explicitly express the count() function: int count = DSL.using(configuration) .select(DSL.count()) .from(Table) .fetchOne(0, int.class); Or, you can use this, if you don’t like mapping the value: int count = DSL.using(configuration) .fetchValue(selectCount().from(Table)); … Read more
How can I search all columns in a table?
SELECT … FROM yourtable WHERE ‘val’ IN (field1, field2, field3, field4, …) if you’re looking for exact full-field matches. If you’re looking for substring matches, you’ll have to go about it the long way: WHERE field1 LIKE ‘%val%’ or field2 LIKE ‘%val%’ etc….
Is it possible to Trim all the values in a column in a single statement?
MS SQL does not have a trim function. You’ll need to use rTrim and lTrim together. update MyTable set Name = lTrim(rTrim(name))
db2 SQLCODE -668 when inserting
To add to James’ answer and save people time looking around, you could execute CALL SYSPROC.ADMIN_CMD(‘REORG TABLE MY_TABLE_NAME’) via any available SQL client (i.e. even over ODBC or JDBC connection) to rectify this problem. However, the connection has to be in autocommit mode and you have to have admin privileges to execute this command. I … Read more
How to subtract hours from a date in Oracle so it affects the day also
Others have commented on the (incorrect) use of 2/11 to specify the desired interval. I personally however prefer writing things like that using ANSI interval literals which makes reading the query much easier: sysdate – interval ‘2’ hour It also has the advantage of being portable, many DBMS support this. Plus I don’t have to … Read more
Get the first and last date of next month in MySQL
Use: SELECT DATE_SUB( LAST_DAY( DATE_ADD(NOW(), INTERVAL 1 MONTH) ), INTERVAL DAY( LAST_DAY( DATE_ADD(NOW(), INTERVAL 1 MONTH) ) )-1 DAY ) AS firstOfNextMonth, LAST_DAY( DATE_ADD(NOW(), INTERVAL 1 MONTH) )AS lastOfNextMonth