MySQL has this incredibly useful yet properitary REPLACE INTO SQL Command. I wonder: Can this easily be emulated in SQL Server 2005? Starting a new Transaction, doing a Select() and then either UPDATE or INSERT and Commit is always a little bit a pain in the a.., especially when doing it in the application and therefore always keeping 2 ...
Hi, I need to rename the database but when I do in PGAdmin : ALTER DATABASE "databaseName" RENAME TO "databaseNameOld" it told me that it cannot. How can I do it? (Version 8.3 on WindowsXP) Update The first error message : Cannot because I was connect to it. So I selected an other database and did the queries. I get a second error ...
I'm getting ORA-01031: insufficient privileges when creating a package my own schema. Shouldn't I have complete control over my schema. If this is not the case, what privileges does my schema need? Thanks in advance. ...
I'm trying to create a table with two columns comprising the primary key in MySQL, but I can't figure out the syntax. I understand single-column PKs, but the syntax isn't the same to create a primary key with two columns. ...
Please note that the same question already has been asked in http://stackoverflow.com/questions/111341/combine-multiple-results-in-a-subquery-into-a-single-comma-separated-value, but the solution involves creating a function. I am asking if there is a way to solve this without having to create a function or a stored procedure. I have ...
When I refer to nested set model I mean what is described here. I need to build a new system for storing "categories" (I can't think of better word for it) in a user defined hierarchy. Since the nested set model is optimized for reads instead of writes, I decided to use that. Unfortunately during my research and testing of nested sets...
I'm being asked to add queryability to a database (Oracle) filled with mostly binary data. So I need to be able to query binary ranges within a blobs of a few kilobytes. I've never done this before, so I'm wondering what are some good practices and pitfalls to consider when starting a project like this. Thanks ...
A colleague would like to query a table in an Oracle database using SQL Server 2000, Enterprise Manager. He knows how to do the whole DTS thing, but doesn't want to go down that route for add hoc queries. Is there another way? ...
I'll simplify the problem as much as possible: I have an oracle table: row_priority, col1, col2, col3 0, .1, 100, {null} 12, {null}, {null}, 3 24, .2, {null}, {null} Desired result: col1, col2, col3 .2, 100, 3 So according to the priority of the row, it overrides previous row values, if given. I'm attempting to work out a solutio...
Using only MySQL, I'm seeing if it's possible run an insert statement ONLY if the table is new. I successfully created a user variable to see if the table exists. The problem is that you can't use "WHERE" along with an insert statement. Any ideas on how to get this working? // See if the "country" table exists -- saving the result to a ...
Hey all, I know you can combine multiple table-selects using a Join statement but is there a way to combine these two queries into one? SELECT Statistics.StatisticID FROM Statistics ORDER BY `SubmittedTime` DESC LIMIT 0, 10 And this one? SELECT COUNT(Votes.StatisticID) FROM Votes WHERE Votes.StatisticID = ? (fluff removed) At th...
In my specific case, I have two kinds of "messages" that I need to retrive and paginate. Let's omit the details, and just say that the first kind is in a model called Msg1 and the other is called Msg2 The fields of these two models are completely different, the only fields that are common to the two models are "date" and "title" (and o...
I have a database with one table, like so: UserID (int), MovieID (int), Rating (real) The userIDs and movieIDs are large numbers, but my database only has a sample of the many possible values (4000 unique users, and 3000 unique movies) I am going to do a matrix SVD (singular value decomposition) on it, so I want to return this databa...
I'm using MS Access to create a database with over 5000 contacts. These contacts are separated up into which employee the contact belongs to, and then again into categories for easy searching. What I want to do is create a button that will open up a query in table form (simple), then have check boxes so an employee can select, for exampl...
I was reading Jeff Atwood's blog on Behold WordPress, Destroyer of CPUs and saw that many people there considered 20 SQL Queries per page load to be a lot. What is the average amount of queries per page nowadays for a highly dynamic page with auto suggest, auto refreshing of data, customized pages, and the kitchen sink? For a simple ...
In my database schema I have an entity that is identified. The identifier can be reused and thus there is a one-to-many relation with the entity. Example: A person can have a nickname. Nicknames are not unique and can be shared amongst many people. So the schema might look like: PERSON id name nickname_id NICKNAME id name The issue i...
I have a function that looks something like this: //iteration over scales foreach ($surveyScales as $scale) { $surveyItems = $scale->findDependentRowset('SurveyItems'); //nested iteration over items in scale foreach ($surveyItems as $item) { //retrieve a single value from a result table and do some stuff ...
Do T-SQL queries in SQL Server support short-circuiting? For instance, I have a situation where I have two database and I'm comparing data between the two tables to match and copy some info across. In one table, the "ID" field will always have leading zeros (such as "000000001234"), and in the other table, the ID field may or may not h...
I'm using VBScript (ASP Classic) and SQL Server; I'm trying to have a section on the website where you can see a count of products at certain price levels. Something like: $50 - $100 (4) $100 - $500 (198) $500 - $1000 (43) For the love of me I cannot figure out a good way to do a query like this. I mean... I know how to get the numb...
Hi all, image this two tables: Table: Item Columns: ItemID, Title Table: Tag Columns: TagID, ItemID, Title Which is the best way (without changing table structure (yes, I don't mind if they are not normalized)) to retrieve a list of items with all their tags attached using less possible query (i.e. not doing 11 queries to retrieve 10...