sql

VBA/Access RecordSet per form record problem

...

MS SQL Server - When is a CURSOR good?

Many times when I've written stored procedures, etc. I use a CURSOR at first and later find some performance issue with my procedure. Every thing I read says CURSORS are awful, cause unnecessary locking, etc. and performance testing proves the same. My question is when do you use a CURSOR and in what situations are they useful or good...

automatically set the value of one variable depending on another variable's value in sql table

I am new to sql and How can I automatically set the value of one variable depending other variable's value. I have price in foods table, and in orders table I want to change the total value of the price according to the # of orders for a specific food. ...

Using ranking-function derived column in where clause (mssql08)

Hoping this is trivial for a SQL-Ninja... Been trying to get the following query working: This is under MSSQL Server 2008 SELECT ROW_NUMBER() OVER (ORDER BY Date_Time DESC) AS RowNumber, * FROM ( SELECT T.A_ID, T.User_Name, T.Date_Time, T.Value, U.ID, U.Name, U.Field1, U.Field2, COUNT(U.ID) OVER () AS TotalRows FRO...

Quickest way to clone row in SQL

I want to clone multiple tables' rows that have a single primary key (PrimKey). What's the quickest way to clone a row in SQL Server 2005? Here's an example, Clone PrimKey1 to get PrimKey2. So I try the following : INSERT INTO PrimKeys SELECT 'PrimKey2' AS PrimKey,* FROM PrimKeys WHERE PrimKey='PrimKey1' But of course the is...

Atomic exclusive SQL record update

I want to update a single record in a table to reflect that a given client session has acquired the record (and now owns it for further updates) within a multi-session environment. I've got this so far: create procedure AcquireRow( @itemNo int, -- Item ID to acquire @sessNo int, -- Session ID @res char(1...

I'm new to SQL & want to join2 query

Table Name : Product Column : Product ID - A1, A2, A3, A4 , Product status (ordered (O). I want to check If Product ID 'A1' product status is ordered or not and if ordered than other 3 ID (A2,A3,A4) should not be updated. In same way I want to check for remaining Product ID ...

MySQL to return only last date / time record

We have a database that stores vehicle's gps position, date, time, vehicle identification, lat, long, speed, etc., every minute. The following select pulls each vehicle position and info, but the problem is that returns the first record, and I need the last record (current position), based on date (datagps.Fecha) and time (datagps.Hora)...

Dependent insertion of data into MySql table

I have 2 tables: user_tb.username user_tb.point review_tb.username review_tb.review I am coding with PHP(CodeIgniter). So I am trying to insert data into review_tb with the review the user had submitted and if that is a success, i will award the user with some points. Well this look like a very simple process. We will first insert th...

How to add a Try/Catch to SQL Stored Procedure

CREATE PROCEDURE [dbo].[PL_GEN_PROVN_NO1] @GAD_COMP_CODE VARCHAR(2) =NULL, @@voucher_no numeric =null output AS BEGIN DECLARE @NUM NUMERIC DECLARE @PNO NUMERIC SET @PNO = 0 DECLARE @PNO1 NUMERIC SET @PNO1=0 -- begin transaction IF NOT EXISTS (select GLDC_...

Does anyone know a neat trick for reusing identity values?

Typically when you specify an identity column you get a convenient interface in SQL Server for asking for particular row. SELECT * FROM $IDENTITY = @pID You don't really need to concern yourself with the name if the identity column because there can only be one. But what if I have a table which mostly consists of temporary data. Lots...

Storing Tags in Database. Store tag once or many times?

I would like a bit more clarification on the toxi method of storing tags in a database – mentioned elsewhere on SO. The database schema is: Table: Item Columns: ItemID, Title, Content Table: Tag Columns: TagID, Title Table: ItemTag Columns: ItemID, TagID This is probably a stupid question (but I don't know the answer)... Should eac...

MySQL: UPDATE with SUM and JOIN

I'm trying to do a SUM and store it in another table. The SUM is simple : SELECT award.alias_id, SUM(award.points) AS points FROM award INNER JOIN achiever ON award.id = achiever.award_id I now want to store that. I figured out how to do it on a row-by-row basis : UPDATE aliaspoint SET points = (SELECT SUM(award.points)...

MySQL date field contains no value - having problem importing

I'm migrating some data into MySQL and the dump from the other database has left a particular date field blank. The table looks like this: +------------------+-------------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+-------------+------+-----+------------...

Working with Time portion of a DateTime Column in SQL Server2008

i have a table in SQL Server 2008 which its name is Table1. Table1 has a column named CreateDate which its data type is datetime. Now, I wanna to get records that their createDate field values are more than for instance 1 hour. ...

SQL Server Insert Query problem

I want to execute this query : INSERT INTO [Order] (FactorId, ProductId, Entity) VALUES((SELECT Top 1 FactorId FROM Factor WHERE UserId = @UserId AND Status = -1), @ProductId, @Entity) but the following error occurs : Subqueries are not allowed in this context. Only scalar expressions are allowed. ...

SQL foreign keys with multiple tables

If I have three tables: music_genres ----------- music_type_id genres [other unique fields] minerals -------- mineral_id mineral [other unique fields] verbs ----- verb_id verbs [other unique fields] and these are populated with: rock jazz funk punk rock boulder stone shale rock wobble shake vibrate Now let's say I was displayin...

When to use "ON UPDATE CASCADE"

Hi All, I use "ON DELETE CASCADE" regular but never use "ON UPDATE CASCADE" as I am not so sure what situation it will be useful. For the sake of discussion let see some code. CREATE TABLE parent ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ); CREATE TABLE child ( id INT NOT NULL AUTO_INCREMENT, parent_id INT, I...

MS Acess 2003 - VBA for Update SQL query?

hey guys, could someone show me the simple update query through vb? I need to add new fields to the table (just 3) and add a couple text boxes on a form so that users can add some additional data relative to the record (which is already what this form is based on). So the first form I have is a form that populates a list, when the user ...

MS Access to SQL Server - Newbie needing advice/direction

So I have been working with multiple different MS Access apps at work (about 8) for different departments. And it looks as though there are going to stand up a SQL Server at work, which makes sense from a capacity/integrations standpoint. Soooo......I have experience with Access, SQL and VBA, but having never used any of it in a SQL Ser...