What does it mean by select 1 from table?
select 1 from table will return the constant 1 for every row of the table. It’s useful when you want to cheaply determine if record matches your where clause and/or join.
sql
mySQL :: insert into table, data from another table?
INSERT INTO action_2_members (campaign_id, mobile, vote, vote_date) SELECT campaign_id, from_number, received_msg, date_received FROM `received_txts` WHERE `campaign_id` = ‘8’
How do I remove the first characters of a specific column in a table?
SELECT RIGHT(MyColumn, LEN(MyColumn) – 4) AS MyTrimmedColumn Edit: To explain, RIGHT takes 2 arguments – the string (or column) to operate on, and the number of characters to return (starting at the “right” side of the string). LEN returns the length of the column data, and we subtract four so that our RIGHT function leaves … Read more
Equivalent of LIMIT and OFFSET for SQL Server?
This feature is now made easy in SQL Server 2012. This is working from SQL Server 2012 onwards. Limit with offset to select 11 to 20 rows in SQL Server: SELECT email FROM emailTable WHERE user_id=3 ORDER BY Id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; ORDER BY: required OFFSET: optional number of skipped … Read more
SQL Query to concatenate column values from multiple rows in Oracle
There are a few ways depending on what version you have – see the oracle documentation on string aggregation techniques. A very common one is to use LISTAGG: SELECT pid, LISTAGG(Desc, ‘ ‘) WITHIN GROUP (ORDER BY seq) AS description FROM B GROUP BY pid; Then join to A to pick out the pids you … Read more
SQL: Subtracting 1 day from a timestamp date
Use the INTERVAL type to it. E.g: –yesterday SELECT NOW() – INTERVAL ‘1 DAY’; –Unrelated: PostgreSQL also supports some interesting shortcuts: SELECT ‘yesterday’::TIMESTAMP, ‘tomorrow’::TIMESTAMP, ‘allballs’::TIME AS aka_midnight; You can do the following then: SELECT org_id, count(accounts) AS COUNT, ((date_at) – INTERVAL ‘1 DAY’) AS dateat FROM sourcetable WHERE date_at <= now() – INTERVAL ‘130 DAYS’ … Read more
How do I dump the data of some SQLite3 tables?
You’re not saying what you wish to do with the dumped file. To get a CSV file (which can be imported into almost everything) .mode csv — use ‘.separator SOME_STRING’ for something other than a comma. .headers on .out file.csv select * from MyTable; To get an SQL file (which can be reinserted into a … Read more
SQL to LINQ Tool [closed]
Edit 7/17/2020: I cannot delete this accepted answer. It used to be good, but now it isn’t. Beware really old posts, guys. I’m removing the link. [Linqer] is a SQL to LINQ converter tool. It helps you to learn LINQ and convert your existing SQL statements. Not every SQL statement can be converted to LINQ, … Read more
Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc
One reason that selecting specific columns is better is that it raises the probability that SQL Server can access the data from indexes rather than querying the table data. Here’s a post I wrote about it: The real reason select queries are bad index coverage It’s also less fragile to change, since any code that … Read more
Update multiple columns in SQL
Try this: UPDATE table1 SET a = t2.a, b = t2.b, ……. FROM table2 t2 WHERE table1.id = t2.id That should work in most SQL dialects, excluding Oracle. And yes – it’s a lot of typing – it’s the way SQL does this.