I'm running into a very strange issue that I have found no explanation for yet. With SQL Server 2008 and using the GROUP BY it is ordering my columns without any ORDER BY specified. Here is a script that demonstrates the situation. CREATE TABLE #Values ( FieldValue varchar(50) ) ;WITH FieldValues AS ( SELECT '4' FieldValue UNION AL...
I want to select and then delete a list of entries in my tables that have case-insensitive duplications. In other words, there are these rows that are unique... ..but they're not unique if you ignore case factor in case. They got in while I wasn't watching. So how can I s...
Is it possible to output values from a cascaded delete in SQL? Something like: DELETE [Families] OUTPUT [deleted].[FamilyID] [deleted].[FamilyName], [Members].[MemberName] FROM [Families] LEFT JOIN [Members] ON [Members].[FamilyID] = [Families].[FamilyID] If there were two families with three total members, it sho...
I've got a query like this: select a, b, c, group_concat(d separator ', ') from t group by a; This seems to work just fine. As I understand it (forgive me, I'm a MySQL rookie!), it's returning rows of: each unique a value for each a value, one b and c value also for each a value, all the d values, concatenated into one string Thi...
In SQL Server I used the following hints inside queries: rowlock (row level locking) updlock (prevents dirty reads) readpast (don't block waiting for a rowlock, go to the first unlocked row) e.g. select top 1 data from tablez with (rowlock,updlock,readpast); Are there equivalent in-query hints for Oracle? ...
How do you establish a constraint where, one column (not the primary key) has to have the same value as another table's column. I'm not quite sure how to phrase it so here's an example: Ex: I have three tables, Employee, Director, Division and Department The structure for the tables are as follows: Employee Id Name DirectorId (FK) ...
First Query: select Batch,Shiplist_Qty,COUNT(X.Batch) as ALLOCATED,(Shiplist_Qty - COUNT(X.Batch)) as Variance from dbo.FG_FILLIN as X where X.Status in('KITTED','ALLOCATED') group by X.Batch,X.Shiplist_Qty Output: Shiplist_Qty ALLOCATED Batch Variance 2 2 CIP2 0 8 6 ...
We have a database with a bunch of wide tables (40-80 columns each) and just found a bug that introduced NULL values into about 500 of the records. The NULL values can appear in any of the columns (all are integer columns, see image below) but these NULL values are causing issues with one of our reporting systems that cannot be changed e...
Hey all, I am trying to delete all but one record from table where name field repeats same value more than 5 times and the address field repeats more than five times for a table. So if there are 5 records with a name field and address field that are the same for all 5, then I would like to delete 4 out of 5. An example: id name address...
my phpmyadmin showing the table type are myisam , but my tables are innodb, Why it is showing as myisam , ...
Suppose I have two tables Table1 and Table2 with the following data. Column1 Column2 Column3 AAA KKK 9 BBB LLL 7 CCC MMM 9 DDD MMM 5 EEE MMM 7 FFF NNN 9 GGG OOO 1 Column4 Column1 TTT DDD TTT BBB UUU EEE VVV BBB WWW AAA WWW BBB XX...
Hello All! Quick question (I hope!): if I use \i to feed an input file into psql, can I get the output from the queries to be saved into a file? If so, how? Thanks!! ...
In Oracle, I can select the top 1 message in a sorted table with select messageid from( select messageid, RANK() over (order by messageid asc) as msg_rank from messages ) where msg_rank=1; And as I discovered in a previous question I can select a row exclusively with select * from messages where rownum < 2 ...
Hi Guys, I was wondering if anybody would have some input on how I could possibly optimize this MySQL query. I think I'm doing the right thing with indexes so don't think I can get this query any faster (it's currently taking more than 3 seconds to run) but would really love somebody with more experience to prove me wrong. Here's the q...
Hi, I have lot of excel files to import in mysql table. Is there any mysql command/procedure which directly takes excel file and import it in table one by one? or any simpler way to to this task in few steps? ...
Hi folks, I have the following DB Schema :- Data is ... Location Table 1. New York 2. London 3. Tokyo 4. Melbourne OtherNames Table (aka Aliases) 1. NYC 1. New York City 4. Home 3. Foo 3. PewPew What I'm trying to do, as SQL, is get the following results :- ID, Name, Name + Aliases eg. 1 | New York | new york nyc new yor...
I got a table like this (just look at Col1 and Col2) ID Col1 Col2 Col3 1 1a 2b vewva 2 1a 2b ds33 3 1c 2d sadp 4 1c 2e c2w 5 1c 2d 2309v 6 1d 2f 2fd3 7 1c 2d 23d3 I need to find duplicates in (Col1, Col2). What I need is some output like this: 1a 2b -...
Hi there, I am just confused in building a SQL query. I tried to run it in pieces successfully but just not able to build it as a whole. This is the case: In table 'userseaches', user Rushi has been searched three times with different searchID's(primary key in 'searchtable') 10, 11, 12 . When searched with searchID 10 got 110 results,...
I'm looking for a simple script that I can use to overwrite one database with another one. We have a master db with master schema and data and every so often a developer wants to blow away his messed up db with a complete overwrite from the master. I currently accomplish this with SQL Server Studio and the GUI controls but I want somethi...
How can I configure my mirror database to publish its data to the same replication database that my primary has ( I need to take my primary database offline for maintenance purposes.)? ...