sql

UPDATE query that fixes orphaned records

I have an Access database that has two tables that are related by PK/FK. Unfortunately, the database tables have allowed for duplicate/redundant records and has made the database a bit screwy. I am trying to figure out a SQL statement that will fix the problem. To better explain the problem and goal, I have created example tables to use...

sql - How do I enforce a two-column constraint?

I have a CustomerID column and an EffectiveDate column in a table. I need the combination of these two to be unique. However, I already have a primary key on an auto-numbered integer column. What is a good way to accomplish my goal? Thanks ...

SQL query , whats this?

I never seen SQL like that before: SELECT * FROM !.tables WHERE id = ! What's it doing? ...

Can I apply NOLOCK with a database-wide setting?

Is there a way to make WITH(NOLOCK) be applied on any SELECT statement run on a particular database? ...

pgSQL query error

i tried using this query: "SELECT * FROM guests WHERE event_id=".$id." GROUP BY member_id;" and I'm getting this error: ERROR: column "guests.id" must appear in the GROUP BY clause or be used in an aggregate function can anyone explain how i can work around this? ...

How to user prefix 'N' for unicode with nvarchar variable in SQL Server?

How to user prefix 'N' for unicode with nvarchar variable in SQL Server? For example: Given this variable: declare @Query1 nvarchar(max) I can assign to it like this: set @Query1 = N'لاحظات' But what if I want to use N@Query1 somewhere? ...

ms-access: displaying column as "something else"

i have the following access sql statement: SELECT * FROM (SELECT [Occurrence Number], [Occurrence Date], [1 0 Preanalytical (Before Testing)], [Cup Type], NULL as '2 0 Analytical (Testing Phase)', [2 0 Area], NULL,NULL FROM [Lab Occurrence Form] WHERE NOT ([1 0 Preanalytical (Before Testing)] IS NULL) in this: NULL as '2 0 Analytical...

ms-access: DB engine cannot find input table or query

here's the query: SELECT * FROM (SELECT [Occurrence Number], [Occurrence Date], [1 0 Preanalytical (Before Testing)], [Cup Type], NULL as [2 0 Analytical (Testing Phase)], [2 0 Area], NULL as [3 0 Postanalytical ( After Testing)],NULL as [4 0 Other], [Practice Code], [Specimen ID #] FROM [Lab Occurrence Form] WHERE NOT ([1 0 Preanalyt...

Tool for convert SQL code to diagram

can any one say some way to convert SQL code to data diagram like ERD ? for som DBMS like MySQL or general SQL ...

How do I Correct for False Positive using MBRContains in a mySQL db?

I've got a mySQL database. I'm testing points to see whether they are in particular polygons. Unfortunately, I'm getting false positives. So, for example, if I test to see if different points are in polygon ABCD, the MBRContains query returns 6 points, when I know for a fact that only 5 of the points are in that polygon. I know that MB...

Table sync and copy into other table

I have two tables. Table A and Table B. They are identical. Ever 10 min i need to check if there any changs happend (New and updated) to Table A and copy into Table B. And also enter in Table C if i see a differance and new. I also need to log if there any new records in Table A to table B and Table C Iam planning to do join and compa...

Large number of UPDATE queries slowing down page

I am reading and validating large fixed-width text files (range from 10-50K lines) that are submitted via our ASP.net website (coded in VB.Net). I do an initial scan of the file to check for basic issues (line length, etc). Then I import each row into a MS SQL table. Each DB rows basically consists of a record_ID (Primary, auto-increm...

Regex to match anything (including the empty string) except a specific given string.

I'd like to test whether a string contains "Kansas" followed by anything other than " State". Examples: "I am from Kansas" true "Kansas State is great" false "Kansas is a state" true "Kansas Kansas State" true "Kansas State vs Kansas" true "I'm from Kansas State" false "KansasState" true ...

ms-access the ability to enter multiple values into a textbox on a form

i have a textbox on a form and i would like the user to be able to choose from different values but also be able to enter their own values, how do i do this? i would like to clarify that the user should be able to enter multiple values into the same textbox ...

ms-access: clear text in all controls with one click

i have some comboboxes and some textboxes on a form i would like to clear all of them with one line of code. is that possible? something like all_controls.text="" ...

How do I programatically verify, create, and update SQL table structure?

Scenario: I have an application (C#) that expects a SQL database and login, which are set by a user. Once connected, it checks for the existence of several table and creates them if not found. I'd like to expand on this by having the program be capable of adding columns to those tables if I release a new version of the program which re...

SQL Aggregate all Purchases for a certain product with same rebatecode

I would like to aggregate all purchases for a certain product that used the same rebatecode (using SQL Server 2005). Assume we have the following table: ID ProductID Product RebateCode Amount 1 123 7HM ABC 1 2 123 7HM XYZ 2 3 124 7HM ABC 10 4 124 ...

How to run a query from the VBA Editor window in Access?

I would like to do something like this: DoCmd.OpenQuery "select * from some_table;" How would I do this? Please keep in mind I don't want to create a new query, save it, and run it. I just want to type the select statement in the VBA code and run it. ...

How to run a query and put the result into a variable

I am running a query from the VBA editor of Access: select max(somerow) from sometable I want to put the result of this query into a VBA variable. How do i do it? ...

enable/disable trigger via code C#

Is there a way to enable or disable a trigger via code? tks. ...