How Do You Delete Duplicate Records In SQL
How to delete duplicate records in sql? ...
sql
MySQL query outputs odd rows only
I have the following PHP method that returns a JSON string from a MySQL query: $sys_words_ref_join_query = mysql_query(" SELECT user_words.*, sys_words.* FROM user_words, sys_words WHERE user_words.sys_words_ref = sys_words.sys_words_ref & user_words.user_info_ref = '1' LIMIT 0, 7 "); $json_array = array(); while($words_obj = my...
Why does Perl's DBIx::Class $resultset->update fail on complex search queries?
I have a Perl DBIx::Class search the looks like this: my $rs = shift->search( { -and => [ { 'for_revocation' => 1 }, [ { status => { 'not_in' => [ 'revoked', 'revoking'] }, }, { ...
Switched from inproc to SQL Server gives me SQL timeouts (no connection max pool size, typed datasets)
Hello everybody. Here is my situation: I have a web and database server (SQL Server 2005) in a LAN. The webserver(iis6) has several application pools and sites run fine with asp.net session inproc. both servers run win 2k3 Now I switched from inproc to SQL Server and I get a lot of SQL timeout expired messages. But only from one web ...
Select Count(Distinct Value) returns 1
I'm designing a query in SSMS 2005 which looks something like this: SELECT COUNT(DISTINCT ColumnName) FROM Table WHERE ColumnName IS NOT NULL When I run the query with COUNT() it returns the value 1. When I run it without COUNT(), SSMS reports the correct value eg 212 records. The column in question is of datatype numeric(16, 0). Fo...
Round date to 10 minutes interval
I have a DATE column that I want to round to the next-lower 10 minute interval in a query (see example below). I managed to do it by truncating the seconds and then subtracting the last digit of minutes. WITH test_data AS ( SELECT TO_DATE('2010-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual UNION SELECT TO_DATE('2010-0...
problem with IN and LIKE in SQL when arguments contain special characters for a language
Hello, I’ve got such a KeyWord table (MS SQL): KeyGuid Qualifier PrimitiveKey DA7E4E27-FDE5-4D43-A365-8A789164A816 tit kirkäna EED58875-FE41-4A18-A93C-A44AA62CEEEE htit kirkänbh A0EB795E-EE23-4990-BAB9-897C93C70CE3 htit kirkänah F7F4632B-AC82-4DEB-B966-BBA8EF4D2C9E tit kirkänb C0EB795E-EE23-4990-BAB9-897C93C70CE3 nam kirkänas E2F...
unix time in mysql
when they say "we will use unix time", does that mean i should create a column like this ? date DATE or date TIME or date DATETIME ? ...
Howto test SQL statements and ensure their quality?
Hi, I am working on some business intelligence reports these days. The data is fetched by ordinary SQL SELECT statements. The statements are getting more and more complex. The data I want to report is partially business critical. So I would feel better, if I could do something to proof the correctness and quality of the SQL statements...
Insert record into table if entry does not exist in another table- with an extra twist
Hi to all you mighty SQLsuperheros out there.. Can anyone rescue me from imminent disaster and ruin? I'm working with Microsoft Access SQL. I'd like to select records in one table (table1) that don't appear in another (table2) .. and then insert new records into table2 that are based on records in table1, as follows: [table1] file_inde...
Is there a tool for refactoring SQL, a bit like a ReSharper for SQL
The sort of stuff I'm after right now is quite basic: Auto format Detect ununsed variables Variable naming convention checking I wouldn't be surprised if there was a tool available that could handle more complex refactorings such as those found in Refactoring Databases, but I appreciate that the added complexities introduced by curr...
How do I get a proper weighted average date in SQL
I use this query to get my average date: CONVERT(DATETIME, AVG(CONVERT(FLOAT, ChargeOffDate))) as [Average C/O Date] Which correctly give me the date '2003-08-12 14:17:22.103'. When I use this query to get the weighted average date CONVERT(DATETIME, SUM(CONVERT(FLOAT, ChargeOffDate) * FaceValue) / SUM(FaceValue)) I get a date like...
SQL function question
I have an sql function and at the end RETURN @_result;. My question is that if I put this REPLACE(@_result, '&', 'eseses') REPLACE(@_result, '-', 'vagyvagy') before RETURN @_result; that is OK? @_result returns a long string and in that string I want to replace & to eseses and - to vagyvagy. ...
linq performance: linq vs t-sql -- where does the time go?
I am testing the Linq performance now and I cannot figure out what for some time is wasted (MS-SQL Server 2005). So here is what I have: single table with clustered and non-clustered indices, all searches are done using columns which are covered in 100% by non-clustered index. I have 10 000 records, and all operations "touches" all reco...
C# - ExecuteNonQuery() isn't working with SQL Server CE
Hello. I got some data inputed by the user that should be added to a Database File (.sdf). I've choose Sql Server CE because this application is quite small, and i didn't saw need to work with a service based database. Well any way. Here goes the code: public class SqlActions { string conStr = String.Format("Data Source = " + ne...
Visual Studio - Open a SQL file with SQL Management Studio in an existing SSMS window?
Hi All, I'm using Visual Studio 2008 and SQL Server 2008. I would like .sql files in my VS projects to open in SQL Management Studio by default. No problem so far: I right-click the file, Open With, Add SSMS, and set it as the default. What I don't like is that it opens a new instance of SSMS every time, and I would like it to open in an...
Programatically backing up (and emailing) a full MySQL database via PHP
I need to take a backup of a MySQL database daily, and preferably have it email itself to an email address of my choosing. Is it possible to do this? What is the SQL to generate a backup file? How can I add this file as an "attachment" to the email which is sent? Thanks for the help, I really need to make these backups! I do know how...
SQL Server Query Question
This might be a very simple question, but am just curious about it. Are these queries both representations of equi join? I find that both returns the same result. Query1: SELECT C.* FROM [Case] A, [Event] B, Notice C WHERE A.CaseID = B.CaseID AND B.EventID = C.EventID Query2: SELECT C.* FROM [Case] A join [Event] B on A.CaseID = B...
MySQL truncation command - unicode characters
Hi all, I am currently trying to adjust the values stored in a table in MySQL. The values stored contain a series of Unicode characters. I need to truncate to 40 bytes worth of storage, but when I try: UPDATE `MYTABLE` SET `MYCOLUMN` = LEFT(`MYCOLUMN`, 40); MySQL is overly helpful and retains 40 characters, rather than 40 bytes. Is t...
Check if role consists of particular user in DB?
I am trying to drop user from role in several databases. It is not sure that user can be in same role in all the databases. How can i check if the user is in the role and if it is drop the user from the role. e.g. IF user exists in role BEGIN drop user from role END ...