sql

How do I update tables in SQL so that related strings match?

Suppose I have 2 tables Table1 ID Path -- ---- 1 PathA 2 PathB 3 PathC 4 PathD Table2 ID Path Table1ID -- ---- -------- 23 PathA 1 24 PathX 2 25 PathC 3 26 PathZ 4 In the above, PathX should be PathB and PathZ should be PathD How do I sync all the path values in Table2 with values in Table 1, the tables could be large so woul...

How to find unrated models with acts_as_rateable

I have a rails application using the acts_as_rateable plugin. I'm stuck on figuring out how to retrieve unrated models using this plugin - however this is more of a general rails/SQL question than specific to this plugin. Acts as rateable adds the following to the schema: create_table "ratings", :force => true do |t| t.integer "r...

Conditional Table Creation in MS-Access

Sqlite has the useful "create table if not exists" syntax. Is there an equivalent in Access, or (as it looks) do I need to build a separate way of checking first? ...

Mysql Query

Hi, I want to fetch all the records in one line with First Name LastName , First Name Last Name, etc........ in mysql Query. For example, mytable looks like this: rec Id First Name Last Name 1 Gnaniyar Zubair 2 Frankyn Albert 3 John Mathew 4 Suhail Ahme...

How to effectively do database as-of queries?

Excuse the long question! We have two database tables, e.g. Car and Wheel. They are related in that a wheel belongs to a car and a car has multiple wheels. The wheels, however, can be changed without affecting the "version" of the car. The car's record can be updated (e.g. paint job) without affecting the version of the wheels (i.e. ...

SQL Query - Where am I going wrong?

I've got two tables, one for openings and the other for bookings. An entry in the bookings table always refers to an opening and there may be multiple bookings for each opening. I would like to extract all the openings that have bookings different from bookingType 'C'. E.g. if an opening has 3 bookings of type A, B and C, it...

Bulk Insert into Oracle database: Which is better: FOR Cursor loop or a simple Select ?

Which would be a better option for bulk insert into an Oracle database ? A FOR Cursor loop like DECLARE CURSOR C1 IS SELECT * FROM FOO; BEGIN FOR C1_REC IN C1 LOOP INSERT INTO BAR(A, B, C) VALUES(C1.A, C1.B, C1.C); END LOOP; END or a simple select...

Increasing Alphanumeric value in user defined function

Hello, I'm trying to code a user defined function under SQL Server 2005 that will increase integer part of alphanumeric value by one. For example, uf_AlphanumericIncrease ('A000299') should return 'A000300'. Here's what I've done so far; ALTER FUNCTION uf_AlphaNumericIncrement ( @ID varchar(10) ) RETURNS VARCHAR(10) AS BEGIN DECL...

SQL Server Automatic Rounding ?

Hi fellows, I am very confused by the following results: PRINT 3.1415926535897931 /180 Console result = 0.01745329251994329500 DECLARE @whatTheHell float(53) SET @whatTheHell = 3.1415926535897931/180 PRINT @whatTheHell Console result = 0.0174533 I don't understand because referring to this: http://msdn.microsoft.com/en-us/libra...

what is faster query (select name .... or select top(1) name...

Hi what is faster query ? select Name from Worker or select TOP(1) Name from Worker I have 1,000,000 records thank's in advance ...

SQL MAX() question

uid timestamp 1 1242420497 1 1243534661 1 1243534858 1 1243611312 1 1243611511 3 1244817764 3 1244819093 1 1244749446 I have this table, and I am lookng to grab the row that has the highest time stamp. I tried using SELECT uid,max(timestamp) FROM `node_revisions` WHERE nid=51 but that returned uid timestamp 1 1244819...

How to iterate over a date range in PL/SQL

I need to write a report that generates summary totals against a table with date ranges for each record. table data: option start_date end_date opt1 6/12/2009 6/19/2009 opt1 6/3/2009 6/13/2009 opt2 6/5/2009 6/6/2009 What I want out is basically this: date option count 6/1/2009 opt1 0 6/1/200...

Safely normalizing data via SQL query

Suppose I have a table of customers: CREATE TABLE customers ( customer_number INTEGER, customer_name VARCHAR(...), customer_address VARCHAR(...) ) This table does not have a primary key. However, customer_name and customer_address should be unique for any given customer_number. It is not uncommon for this table to con...

Difference between a inline function and a view

I am a newbie in using functions and it appears to me that an inline function is very similar to a view. Am I correct? Also, can I have UPDATE statements within a function? ...

Using a variable for table name in 'From' clause in SQL Server 2008

I have a UDF that queries data out of a table. The table, however, needs to be definable as a parameter. For example I can't have: Select * From [dbo].[TableA] I need something like: Select * From [dbo].[@TableName] The above line doesn't work, and also the UDF prohibits me from setting the query as a string and calling exec(). I can...

Am I translating Ansi OUTER JOIN syntax correctly to older Sybase (*=) join syntax?

Assuming this is the correct Ansi SQL syntax for a left outer join: SELECT * FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID And this is the correct Ansi SQL syntax for a right outer join: SELECT * FROM employee RIGHT OUTER JOIN department ON employee.Departme...

PostgreSQL: Select a single-row x amount of times

A single row in a table has a column with an integer value >= 1 and must be selected however many times the column says. So if the column had '2', I'd like the select query to return the single-row 2 times. How can this be accomplished? ...

MySQL query help: how to deal with data in most-recent-row-per-day from a big dataset

Hi everyone, I have a somewhat complicated assortment of tables for which I need to do some SQL query construction/optimization. Currently a lot of the logic being used to obtain the results we need is being done at the app layer, which is resulting in terrible performance due to full table traversals, etc. SQL is not my strong suit, ...

"Invalid character value for cast specification" for linked 2008 SQL server in 2005 instance

I am attempting to create a linked server from a 2005 to 2008 Microsoft SQL Server. I do this regularly for 2005 instances, but this is the first step of my long journey into SQL 2008. I am able to create the linked server as any other linked server, I receive no errors, however any time I try to use the linked server for anything (a sim...

What tool do you use to model your domain objects?

I am looking for a tool that will let me model my domain as well as map exports of it to other formats. Is there anything good out there? ...