delete all from table
what's faster? DELETE * FROM table_name; or DELETE * FROM table_name where 1=1; why? does truncate table work in access? ...
sql
Getting the max(price) for each item ordered, given multiple different prices for any ordered item
If I have an items_ordered table that looks like this: items_ordered customerid order_date item quantity price 10330 30-Jun-1999 Pogo stick 1 28.00 10101 30-Jun-1999 Raft 1 58.00 10298 01-Jul-1999 Skateboard 1 33.00 10101 01-Jul-1999 Life Vest 4 125.00 10299 06-Jul-1999 Parachute 1 1250.00 10339 2...
Mysql SQL join question
I am trying to find all deals information along with how many comments they have received. My query select deals.*, count(comments.comments_id) as counts from deals left join comments on comments.deal_id=deals.deal_id where cancelled='N' But now it only shows the deals that have at least one comment. What is the problem? ...
Getting date string from getdate method
Hi, I need date string using sql statement like.. select getDate() this will return 2010-06-08 16:31:47.667 but I need in this format 201006081631 = yyyymmddhoursmin How can I get this? Thanks ...
I need to remove a unique constraints that I don't know the names of
I maintain a product that is installed at multiple locations which as been haphazardly upgraded. Unique constraints were added to a number of tables, but I have no idea what the names are at any particular instance. What I do know is the table/columnname pair that has the unique constraints and I would like to write a script to delete an...
SQL error: String or binary data would be truncated
I got a table variable @RQ, I want it updated using a table-valued function. Now, I think I do the update wrong, because my function works... The function: ALTER FUNCTION [dbo].[usf_GetRecursiveFoobar] ( @para int, @para datetime, @para varchar(30) ) RETURNS @ReQ TABLE ( Onekey int, Studnr nvarchar(10), Stu...
PostgreSQL function to iterate through/act on many rows with state
I have a database with columns looking like: session | order | atype | amt --------+-------+-------+----- 1 | 0 | ADD | 10 1 | 1 | ADD | 20 1 | 2 | SET | 35 1 | 3 | ADD | 10 2 | 0 | SET | 30 2 | 1 | ADD | 20 2 | 2 | SET | 55 It represents actions happe...
Creating a foreign key in MySQL produces error:
I'm trying to create a foreign key on a table in MySQL and I'm getting a strange error that there seems to be little info about in any of my searches. I'm creating the key with this (emitted from mysql workbench 5.2): ALTER TABLE `db`.`appointment` ADD CONSTRAINT `FK_appointment_CancellationID` FOREIGN KEY (`CancellationID` ) REFE...
Convert Military time to string representation
I have an column declarated as int (called HourMil) wich store the time in military format. i need convert this values to an formated string (HH:MM) example HourMil = 710 -> must be 07:10 HourMil = 1305 -> must be 13:05 Actually i am using this code (and works ok) for convert the column HourMil to the string representation. SELEC...
SQL query trick
I need to do select * from xxx where name in (a,b,c...); but I want the result set to be in the order of (a,b,c...). is this possible? ...
sql select from a large number of IDs
I have a table, Foo. I run a query on Foo to get the ids from a subset of Foo. I then want to run a more complicated set of queries, but only on those IDs. Is there an efficient way to do this? The best I can think of is creating a query such as: SELECT ... --complicated stuff WHERE ... --more stuff AND id IN (1, 2, 3, 9, 413, 4324, ....
T-SQL For Each Alternative?
I need to take data from one table and import it into another table. In pseudocode, something like this: For Each row in table1 If row.personid is in table2 then update table2.row Else insert row into table2 End If Next What is the best way to do this in T-SQL? As I understand it T-SQL doesn't support For Each..Next, so what alt...
Getting Popular Topics on a Custom Made Forum
For this website we're working on, we're trying to get the most popular topics (based on how many posts have been made in them within the last 24 hours). We have a medium to large based forum, and the current MySQL query looks like this: SELECT `forums_topics`.`id`,`forums_topics`.`name`, ( SELECT COUNT(`id`) FROM `...
SQL Update help
I have a really simple question, is it possible to update a table with new values using just one update statement. Say for example I have a table with author, title, date, popularity. Now I got some new data which has author name, title corresponding new popularity. How do I update the table now in one statement. Note that author and ti...
sql generate unique table/view name
I want to create some mad robust code. I want to take a query as a string, create a temporary view / table to store the results, use it, then drop the table. I want to use a name that is guaranteed to not already exist in the database. Is there an SQL command to generate a unique table name? I'm using postgresql if this is implementation...
SQL get data out of BEGIN; ...; END; block in python
I want to run many select queries at once by putting them between BEGIN; END;. I tried the following: cur = connection.cursor() cur.execute(""" BEGIN; SELECT ...; END;""") res = cur.fetchall() However, I get the error: psycopg2.ProgrammingError: no results to fetch How can I actually get data this way? Likewise, if I just have ma...
IN statement performance in PostgreSQL (and in general)
I know this has probably been asked before, but I can't find it with SO's search. Lets say i've TABLE1 and TABLE2, how should I expect the performance of a query such as this: SELECT * FROM TABLE1 WHERE id IN SUBQUERY_ON_TABLE2; to go down as the number of rows in TABLE1 and TABLE2 grow and id is a primary key on TABLE1. Yes, I know...
Using LIKE in MySQL SELECT fields
I've been trying to select the status of doing a LIKE comparison: SELECT (`my_column` LIKE '%thepattern'%) AS `did_match` Thanks! Edit: Just to clarify this was used so I could get this: SELECT (CASE WHEN `title` LIKE '%keyword%' THEN 20 ELSE 0 END) + (CASE WHEN `desc` LIKE '%keyword%' THEN 10 ELSE 0 END) AS `match` F...
not including null values in sql join
Hi, I have two tables CustomerAddress(CustomerId, City, Country) and CustomerTransactions(TransactionId, CustomerId, CustomerContact). Here are the values in the tables: For CustomerAddress: 1001, El Paso, USA 1002, Paris, France 1003, Essen, Germany For CustomerTransactions: 98, 1001, Phillip 99, 1001, NULL 100, 100...
is it possible to programmatically check the service provider for a cell number?
is there a database or API available where i can enter a cell phone number and it will spit out whoever the providor is like tmobile or verizon? ...