sql

How do I move (INSERT+DELETE) the last n rows from a table in Jet SQL / Access 2003?

Hi, I have a system that receives input from the public each day. Each morning when it starts up I want to run a VB script that moves every input beyond the latest 500 entries into a backup table. This is kind of a complete archive of the systems activity. I want to move (INSERT row from table 'active' into table 'archive' and then D...

Searching for keywords in two MySQL columns

I have a user table with columns named *first_name* and *last_name*. SELECT * FROM users WHERE first_name LIKE '%keywords%' OR last_name LIKE '%keywords%' Using the above, if I search for "John" or for "Doe" I'll get a hit. But if I search for "John Doe" I will get 0 results. How can I search MySQL in a way that will mat...

MySQL - Limit the amount of rows in a join?

Not sure Looking for some guidance here... I have the following tables: site_text id site_id text date_added 1 1 ... 2010-02-02 10:01:00 2 1 ... 2010-02-02 10:01:20 3 2 ... 2010-02-02 10:01:00 4 1 ... 2010-02-02 10:01:40 5 2 ... 2010-02-02 10:02:00...

php mysql update statment generation from form values

I am creating a very small database abstract layer, i want to send an array of variables that i obtained from a form, and generate an update sql statement, and eventually execute it. What do I put on the second to last line below? for example. $table name = "user"; $username = $_post['username']; $password = $_post['password']; $email ...

SQL Server: table variable used in a inner join

Hi folks: What is the problem with following SQL. Can table variable not be used in JOIN clause? Error msg is of "Msg 170, Level 15, State 1, Line 8 Line 8: Incorrect syntax near 't1'." Declare @t TABLE ( _SportName varchar(50), _Lang varchar(3) ) insert @t VALUES('Basketball', 'ENG') -- ENG UPDATE tblSport t1 SET ...

SQL Server 2008 - Default column value - should i use null or empty string?

For some time i'm debating if i should leave columns which i don't know if data will be passed in and set the value to empty string ('') or just allow null. i would like to hear what is the recommended practice here. if it makes a difference, i'm using c# as the consuming application. ...

sql order by numeric string .

I am using oracle 10. I need to sort my result set according to two numeric string fields. one sort criterion field holds data like this: FIELD1: FO-100001001001 FO-100001002001 FO-100001003001 SQ-200001003001 FC-102001003001 the other : FIELD2: 000203 000567 349990 I need to combine the two criterion , the first criterion take t...

What is a dynamic SQL query, and when would I want to use one?

What is a dynamic SQL query, and when would I want to use one? I'm using SQL Server 2005. ...

What will be the benefits of NHibernate in a data retrieval only scenario?

Hi All, We have been suggested to use NHibernate for our project. But the point is our application will only be retrieving the data from the database so are there any benefits of using NHibernate in this scenario? One more thing, what is the query execution plan in NHIbernate does it support something like prepared statements or the so...

SQL Server creating a temporary table from another table

I am looking to create a temporary table which is used as an intermediate table while compiling a report. For a bit of background I am porting a VB 6 app to .net To create the table I can use... SELECT TOP 0 * INTO #temp_copy FROM temp; This creates an empty copy of temp, But it doesn't create a primary key Is there a way to crea...

best way in producing a master script for SQL

hi, i want to extract specific database tables & stored procedures into one master script. Do you know any software that can help me do this faster? I've tried using the SQL Database publishing tool, but it's not that efficient since its gathering tables that I didn't select. ...

SQL - many-to-many table primary key

This question comes up after reading a comment in this question: http://stackoverflow.com/questions/2190089/database-design/2190101 When you create a many-to-many table, should you create a composite primary key on the two foreign key columns, or create a auto-increment surrogate "ID" primary key, and just put indexes on your two FK co...

Query parent and child entries of one element in a single row

Hi there, I'm searching for the best way to display parent and child entries in one row. Example: Table A ID | PARENT_ID | VALUE ============================= 1 | | A 2 | 1 | B 3 | 2 | C 4 | | D 5 | 4 | E So I want to get the following result: ID ...

SQL: Exchange column values

Want to clear some concepts about SQL internals. Suppose I have a table as: ---------tblXY----------- X int Y int Now it has records as: X Y --- 1 4 2 3 3 2 4 1 And I want the resulting table to be: X Y --- 4 1 3 2 2 3 1 4 So I wrote the query as: UPDATE tblXY SET [X] = Y ,[Y] = X and got the required result. But how d...

MySQL Filter results

Hey, I have a table in my SQL in the following structure called actions: +----+--------+------+---------+ | id | action | type | user_id | +----+--------+------+---------+ | 1 | 5 | 4 | 1 | | 2 | 6 | 4 | 1 | | 3 | 5 | 4 | 2 | | 4 | 0 | 0 | 2 | | 5 | 0 | 1 | ...

Determine that a database is currently mirroring or not?

Is there a T-SQL script to determine a database is currently mirroring or not? Is there a way to determine its partner? Thank you very much. ...

SQL query for dynamic insert row

I am having data like: ItemCode Attribute PositionID ITEM-000032 CHESTSIZE 1 ITEM-000032 JACKETLEN 2 ITEM-000042 CHESTSIZE 1 ITEM-000042 JACKETLEN 2 **ITEM-000049 SLACKWAIST 1** ITEM-000071 CHESTSIZE 1 ITEM-000071 JACKETLEN 2 ITEM-000074 CHESTSIZE 1 ...

Specifying which record to return from a GROUP BY clause

EDIT TO CLARIFY I am probably misunderstanding the use of GROUP BY so I'll just rephrase my question without making assumptions on how to solve the problem: I have a list of term_ids and a table containing objects (which have an object_id PK and term_id as FK among other fields), I need to extract the object with the highest object_id ...

how to avoid & and % in search

Hi i am getting query output as all Records when i put & and % sign in my search string I am using oracle and mysql as a database How i will avoid this , this is dynamically generated query snipit using java WHERE 0 = 0 AND (LOWER (business_keywords) LIKE '%&%'); and WHERE 0 = 0 AND (LOWER (business_keywords) LIKE '%%%'); thank...

SQL CTE counting childs recursion

I'd like (using cte) to count children in table in that way to have at parent level number of all children including theirs children. Is there any sample available? ...