sql

MySQL - Select Concat an entire row

How can I select and concat every field in a row? I want to do something similar to this: SELECT concat(SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tbl_name') as single FROM tbl_name ..but obviously the above doesn't work. Any suggestions? ...

MySql error 121 while restoring truncated database from dump

Hi! I need to create a copy of PROD database in my DEV environment. I've made a MySQL dump, truncated old database to remove all structure & data. But during import I get such error: http://pastebin.com/m1ff0e920. After googling, I've discovered, that this problem is for duplicated names of the foreign keys. But 1) There is no FK in this...

MySQL transaction query help

This problem is baffling me: BEGIN; INSERT INTO sub_users(user_id, email) SELECT user_id FROM users WHERE email='[email protected]', '$email'; COMMIT; Normally, I have multiple statements in that transactions, but I've removed those for clarity. I get this error: #1064 - You have an error in your SQL syntax; check the manual that...

Why use Select Top 100 Percent ?

I understand that prior to SQL Server 2005, you could "trick" SQL Server to allow use of an order by in a view definition, by also include TOP 100 PERCENT in the select clause. But I have seen other code which I have inherited which uses SELECT TOP 100 PERCENT ... within dynamic SQL statements (used in ADO in a ASP.NET apps, etc). Is the...

Finding top n for each unique row

I'm trying to get the top N records for each unique row of data in a table (I'm grouping on columns b,c and d, column a is the unique identifier and column e is the score of which i want the top 1 in this case). a b c d e 2 38 NULL NULL 141 1 38 NULL NULL 10 1 38 1 NULL 10 2 38 1 NULL 1 1 38 1 ...

Help with hard sql query to update based on daily totals to summary table

The following are my sql server 2005 table structures: Products (productID INT PK, ...) ProductBids (productID INT, userID INT, Created DATETIME) Users(UserID INT PK, TOTALBIDS INT) Each day a user can bid as many times as they want on all the products. There is a sql job that is run periodically, that counts the total bids a user ha...

Unique ID SQL Buddy

What is the best way to create a unique id column for table records in SQL Buddy? I am new to all of this so excuse the ignorance. Is there some sort of auto number generator or something? I am assuming that the php script will check the table and insert the next number or something. Again, I am learning right now after using Caspio for ...

SQL update to record with nearest data

In SQL server 2008: Suppose I have two tables. Table1 has 3 fields: Name, Date1 and Date2. Currently, all the Date2 entries are NULL. (Name, Date1) form a unique key. Table2 has 2 fields: Name and Date2. (Name, Date2) form a unique key. Every "Name" in Table1 has at least one corresponding entry in Table2. Now, I want to update all...

How to convert this SQL statement to LINQ?

I have no idea how to convert this SQL statement to LINQ that uses OUTER APPLY and TOP. Can somebody give an idea how deal with it. Thanks! SELECT Cust.CustomerName, Ord.OnlineOrderTitle, Pro.ProductTitle, Pic.PictureFilename, PCom.PictureCommentText, Ord.OnlineOrderDateAdded FROM Customer as Cust OUTER APPLY (SELECT * FROM OnlineO...

Query is slow while doing a NOT IN on a nested SELECT from another table

SELECT problems . * , users.id AS new_user_id, users.nick AS nick FROM problems, users WHERE problems.deleted =0 AND problems.topic_id =1 AND problems.user_id = users.id AND problems.id NOT IN ( SELECT DISTINCT (problem_id) FROM problems_attempted WHERE user_id =1 AND total_questions = ( attempted_right + attempted_wrong + skippe...

mysql set a flag for each match

I have the following query: SELECT users_extra.first_name, users_extra.last_name FROM (branches, users_extra) WHERE ((branches.manager_id = users_extra.userid) OR (branches.sales_manager_id = users_extra.userid) OR (branches.admin_manager_id = users_extra.userid) OR (branches.ops_manager_id = users_extra.userid) OR (branches.export_mana...

Need help with complex sorting in SQL

Hi, I have a complex sorting problem with my SQL statement. I have a table with the following columns. No Time Value -- ---- ----- 1 0900 '' 2 1030 '' 3 1020 '' 4 1010 '' 5 1100 '' 1 1015 'P' 2 1045 'P' I want to sort this table by doing the following steps. Select rows from the ...

Sending Mail Using SQL Server 2000

Duplicate of: http://stackoverflow.com/questions/1620508/automated-smtp-not-mapi-emails-using-sql-server-job-scheduler I want to send mail using Stored Procedure using SQL Server 2000 using windows smtp component. I don't want third party component.My SMTP server name ='mail.met.net'.how to send mail using the sql server 2000? CREATE...

How to show and search for hidden characters (line feeds, etc) in SQL

I have a large MySQL database with lots of text (like '[new line]Normal') that I want to find & replace. However, I can't find it with any of the following searches, because I'm not sure what characters are used where the [new line] occurs - hence my question. SELECT * FROM table WHERE field LIKE "%<!--[if gte mso 9]><xml>\nNormal%"; S...

Listing blog entries by month

I am writing custom code to create a blog. I need the archives page to list all the blog entries by month. I cannot come up with a way to do that. I guess it should not be too tough as it is a common feature on all blogs. The table structure is (postid, posttitle, publishdate, .....) ...

SQL Server Where Clauses

Hi, When running a select query (on SQL SERVER 2000+) with multiple where statements such as SELECT * FROM TABLE1 WHERE TableId = @TableId AND Column1 = @Column1 AND Column2 = @Column2 AND Column3 = @Column3 etc. ...does the query calculate each and every one of the where clauses even if ...

Does NHibernate interoperate with Hibernate? If not is there a framework out there for both Java and .NET?

Say you had: a database a bunch of .NET Windows GUI programs a bunch of Java web applications you could use Hibernate for the Java stuff and NHibernate for the .NET stuff - but would the two actually interoperate or would they be entirely different stacks? If not then is there a persistence framework out there that lets Java and .NE...

DELETE, INSERT vs UPDATE || INSERT

I have a table in SQL that will be updated daily. Which is better, to delete the existing table and re-insert old and new values, or update existing values if changed, and add new ones? ...

SSIS: How do I pull a SQL statement from a file into a string variable?

I have a few SQL statements stored in text files. How do I pull these files into a string variable in SSIS so that I can use the same query in multiple places? Answer to questions: The queries are long and complex, something I'd prefer to edit in a real text editor, not inside the SSIS text boxes. I'd also like the queries to be edit...

Move tables from production to development

I have an application and I want to move some of the tables from the production database to the development database to refresh the data on development. I don't need to move all of the tables. This application is still running on SQL2000 and planned to be upgraded next year. I have SQL2008 installed on my workstation. I was thinking ...