Update Query using more than one table - Database Discussion Boards

Re: How to get max on each row [modified]

xoxoxoxoxoxox26-Jan-07 11:08

xoxoxoxoxoxox

26-Jan-07 11:08

Both solutions are perfect! Thank you very much for your answer.

-- modified at 17:21 Friday 26th January, 2007

Int value as 0001

javierarka26-Jan-07 9:40

javierarka

26-Jan-07 9:40

How can i crate aint value that will display the value as 0001 to 9999
without omitting the zeroes

Thanks

javierarka

Re: Int value as 0001

andyharman26-Jan-07 9:52

andyharman

26-Jan-07 9:52

In SQL-Server:
set @myString = Right('0000' + Convert(varchar(4), @MaxKey), 4)

In VbScript:
myString = Right("0000" & intNumber, 4)

Column ID in a Table wiht autoincrement value predetermined

javierarka26-Jan-07 9:23

javierarka

26-Jan-07 9:23

I need to create a Column id in my table.

This Column ID has to be a 'sa0200701260001' where sa0 is a value i give to it 20070126 is the current date and 0001 is a number that will increment everytime this statement is called

can somebody tell me how can i make this incrmented value.

Thanks

Javierarka

Re: Column ID in a Table wiht autoincrement value predetermined

andyharman26-Jan-07 9:50

andyharman

26-Jan-07 9:50

Which database engine are you using? If you were using a SQL-Server stored procedure then the code would look something like:

create procedure MyRecordInsert
@Prefix varchar(3),
@OtherData varchar(20)
as begin
   set nocount on
   declare @PrimaryKey varchar(15), @MaxKey int
   Begin tran

   --Create first part of primary key (prefix + date).
   set @PrimaryKey = @Prefix + Convert(varchar(8), GetDate(), 112)

   --Find the higher number that has been allocated today.
   --Use locking to prevent anyone else inserting until we have finished
   --our transaction.
   select @MaxKey = Convert(Int, Substring(Max(PrimaryKey), 12, 4))
      from MyTable with (tablock, holdlock)
      where PrimaryKey like @PrimaryKey + '%'

   --Increment the highest number, and then append to key.
   set @MaxKey = IsNull(@MaxKey, 0) + 1
   set @PrimaryKey = PrimaryKey +
         Right('0000' + Convert(varchar(4), @MaxKey), 4)

   --Insert record into the database.
   insert into MyTable (PrimaryKey, OtherData)
      values (@PrimaryKey, @OtherData

   Commit tran
end

You will need to add some error handling. The general approach is valid for other database engines (although the syntax would obviously be different). The combination of a transaction and locks should allow you to allocate new keys without clashing with other users. If you only have a small number of users then you might be able to get away with a simpler approach.

Hope this is helpful.
Andy

Re: Column ID in a Table wiht autoincrement value predetermined

Pete O'Hanlon26-Jan-07 10:05

Pete O'Hanlon

26-Jan-07 10:05

You could always do it using something like this:

INSERT INTO myTable(ID, Item1, Item2)
SELECT 'sa0' + CONVERT(DATETIME, GETDATE(), 112) + COUNT(*),
@Item1, @Item2
FROM myTable

the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer

Deja View - the feeling that you've seen this post before.

Correct way of databinding of foreign key?

TrooperIronMan26-Jan-07 8:32

TrooperIronMan

26-Jan-07 8:32

This thing bother me some time already

Say I have form that should display data about some student; one of thing it need to display is cityID which is just foreign key to some city in table city.

How can I make it display name of city instead of ID, but write ID to table student.

I tried to do this logical way but never worked, first if there is some data in base it'll show FK value instead on city name, second when I drop down list I get names of cities, but when I pick one from list I never can continue even though I selected value member as ID field not name field (in parent table).

I tried any combination available still doesn't work as expected.

all data is show in details mode and combo box is for cityID value...

Any idea?

Update Query using more than one table

sayianjin26-Jan-07 5:55

sayianjin

26-Jan-07 5:55

Hi All,

I am trying to run an update query from Oracle SQL Plus, I need to change a reference ID (in table Equipment) from A to B where the ContractID (in table Contract) is 1234, however the link between Contract table and Equipment is done through 2 other tables, the query is:

UPDATE EQUIPMENT SET REF_ID = ‘B’
WHERE EXISTS
(SELECT CONTRACT.CONTRACT_ID, EQUIPMENT.REF_ID
FROM ((CONTRACT LEFT JOIN CONTRACT_PERIOD ON CONTRACT.CONTRACT_ID = CONTRACT_PERIOD.CONTRACT_ID) LEFT JOIN CONTRACT_EQUIP_PERIOD ON CONTRACT_PERIOD.CONTRACT_PERIODOID = CONTRACT_EQUIP_PERIOD.CONTRACT_PERIODOID) LEFT JOIN EQUIPMENT ON CONTRACT_EQUIP_PERIOD.EQUIPMENTOID = EQUIPMENT.EQUIPMENTOID
WHERE (((CONTRACT.CONTRACT_ID)=’1234’) AND ((EQUIPMENT. REF_ID)=’A’)));

when I run this query the rather than change 2 records, it changes about 233,000 of them (Good things I'm using the test Database)

If you could tell me where I'm going wrong, I would really appreciate it.

Many thanks
Sayian

Ignorance is darkness.
Knowledge is light.

Re: Update Query using more than one table

andyharman26-Jan-07 7:25

andyharman

26-Jan-07 7:25

I think you need the following:

UPDATE EQUIPMENT SET REF_ID = ‘B’
   WHERE EQUIPMENTOID IN (
      SELECT CONTRACT_EQUIP_PERIOD.EQUIPMENTOID
      FROM CONTRACT
      LEFT JOIN CONTRACT_PERIOD
      ON CONTRACT.CONTRACT_ID = CONTRACT_PERIOD.CONTRACT_ID
      LEFT JOIN CONTRACT_EQUIP_PERIOD
      ON CONTRACT_PERIOD.CONTRACT_PERIODOID = CONTRACT_EQUIP_PERIOD.CONTRACT_PERIODOID
      WHERE CONTRACT.CONTRACT_ID = 1234
   )
   AND EQUIPMENT.REF_ID= 'A' ;

Your original SQL was saying "Do any equipment rows exist for the criteria? Right now update everything single equipment record!"

Regards
Andy

Cross databae table relation ship

TUX2K25-Jan-07 23:37

TUX2K

25-Jan-07 23:37

I need to grab data from a table not in my database.
The other database is on the Ms SQL server.
Now I know how to access it, but I was just thinking what would be the best method for accessing the data.
First I was thinks about just working with the tables in the other database and using sored procedures to insert and access the data.
The second option is to make views to the other database and use them as if there locale tables.

The second option makes it easy to make any change later, but this probably not happen and I could just act a an overhead.

Anyone a pointers on the best way to do this?

Convert getdate()

javierarka25-Jan-07 10:59

javierarka

25-Jan-07 10:59

How can i convert a getdate() result to a style of yyyymmddhhmmss

Thanks

Javierarka

Re: Convert getdate()

andyharman25-Jan-07 22:49

andyharman

25-Jan-07 22:49

select replace(convert(varchar(10), GetDate(), 112) + convert(varchar(8), GetDate(), 114), ':', '')
Regards
Andy

Re: Convert getdate()

Szemi9025-Jan-07 22:59

Szemi90

25-Jan-07 22:59

select replace(replace(replace(convert(varchar(19),getdate(),120),'-',''),':',''),' ','')

CDaoDatabase Create Field

DKScherpy25-Jan-07 9:58

DKScherpy

25-Jan-07 9:58

Ok, I have a concurrency problem (I think) while trying to create a field using CDaoDatabase & CDaoRecordset.

Long and short of it is that I need to create a field in an access database while still keeping a recordset that is connected to the table open. When I create the field using the following code:

CDaoTableDef tableDef(&m_daoDB);<br />
		tableDef.Open(sTableName);<br />
		tableDef.CreateField(newField);

I also have a recordset open on the same table. The last line throws an exception with a message: The database engine could not lock table 'Test Datatypes' because it is already in use by another person or process.

Any help or thoughts on that would be wonderful. Thanks in advance.

Increment Field Value

achimera25-Jan-07 9:39

achimera

25-Jan-07 9:39

Is there a way to increment a field that is an INT value, without having to query the existing value and then add one and write it back?

For example, if I have a table like this:

Column Name | Datatype
======================
ID          | INT (Identity)
Count       | INT

Then is there a way I can do something like this?

INCREMENT Count FROM MyTable WHERE ID='1'

I'm using Microsoft SQL Server 2005. Thanks in advance for any help!

Re: Increment Field Value

PIEBALDconsult25-Jan-07 9:55

PIEBALDconsult

25-Jan-07 9:55

UPDATE MyTable SET Count=Count+1 WHERE ID='1'

Re: Increment Field Value

achimera25-Jan-07 10:06

achimera

25-Jan-07 10:06

Thanks!!!

call function from select join

montu337725-Jan-07 3:51

montu3377

25-Jan-07 3:51

Hi,

This is quick question regarding calling function from join.

In my project i am facing one situation where i need to use join
between one temporary table and database table.From that join statement
i need to call one function which taking 4 input parameters of temporary table and 4 input parameters of database table and returns one varchar2 value.

Now here is question

Let's say i creates one join statement like :

"Select temp_table.firstname,temp_table.lastname,temp_table.ssn,
db_table.firstname,db_table.lastname,db_table.ssn,
getmatchtype(temp_table.firstname,.. ,..,..,..,db_table.firstname,..,..,..)
from temporary_table ,database_table
where
(
/*
some code in where clause which joins temporary table and database table.
*/
)

Now my question is that do my

getmatchtype(temp_table.firstname,.. ,..,..,..,db_table.firstname,..,..,..)

function returns same value for value based on input parameter or it returns
different differnt value every time.

Or can some one say me that how my function works in join query???

Actually , my requirement is that depends on where clause of join,
function takes input parameters and return back to select query and
everytime function should return new value for one by one record.

Is this join works of not for that??

Can some body help me out for this??

Thankx.

regards,
montu3377

Re: call function from select join

Paddy Boyd25-Jan-07 4:29

Paddy Boyd

25-Jan-07 4:29

My first idea would be to try it and see, but I think i see what you mean and your function should produce a different value based on the row, as long as the input parameters are different, of course...

SCOPE_IDENTITY() Always Returning 1

Farm Developer25-Jan-07 0:43

Farm Developer

25-Jan-07 0:43

I have the Stored Procedure below which I run using the code below that but each time I run the procedure the row gets added to the table but the @Identity parameter is always the value 1. There is a column on the Invoice table called InvoiceID which has Identity set as Yes.

Am I doing something wrong?

Thanks
Paul

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author,,name>
-- Create date: <create date,,="">
-- Description: <description,,>
-- =============================================
ALTER PROCEDURE [dbo].[InsertInvoice]
-- Add the parameters for the stored procedure here
@IClientID int
,@InvoiceNumber varchar(15)
,@InvoiceDate datetime
,@PaymentStatus tinyint
,@Description varchar(255)
,@InvoiceNotes text
,@ICustomCheck1 bit
,@ICustomCheck2 bit
,@ICustomCheck3 bit
,@ICustomCheck4 bit
,@ICustomCheck5 bit
,@ICustomCheck6 bit
,@IsAgent bit
,@Identity int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO [BreedSociety].[dbo].[Invoice]
([IClientID]
,[InvoiceNumber]
,[InvoiceDate]
,[PaymentStatus]
,[Description]
,[InvoiceNotes]
,[ICustomCheck1]
,[ICustomCheck2]
,[ICustomCheck3]
,[ICustomCheck4]
,[ICustomCheck5]
,[ICustomCheck6]
,[IsAgent])
VALUES
(@IClientID
,@InvoiceNumber
,@InvoiceDate
,@PaymentStatus
,@Description
,@InvoiceNotes
,@ICustomCheck1
,@ICustomCheck2
,@ICustomCheck3
,@ICustomCheck4
,@ICustomCheck5
,@ICustomCheck6
,@IsAgent)

SET @Identity = SCOPE_IDENTITY()
END

SqlCommand cmd = dCon.CreateCommand();
cmd.CommandText = "InsertClient";
cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.Parameters.Add("@HoldingNumber", SqlDbType.VarChar).Value = client.HoldingNumber;
cmd.Parameters.Add("@TradingTitle", SqlDbType.VarChar).Value = client.TradingTitle;
cmd.Parameters.Add("@Surname", SqlDbType.VarChar).Value = client.Surname;
cmd.Parameters.Add("@ContactName", SqlDbType.VarChar).Value = client.ContactName;
cmd.Parameters.Add("@Address1", SqlDbType.VarChar).Value = client.Address1;
cmd.Parameters.Add("@Address2", SqlDbType.VarChar).Value = client.Address2;
cmd.Parameters.Add("@Address3", SqlDbType.VarChar).Value = client.Address3;
cmd.Parameters.Add("@Address4", SqlDbType.VarChar).Value = client.Address4;
cmd.Parameters.Add("@Postcode", SqlDbType.VarChar).Value = client.Postcode;
cmd.Parameters.Add("@Tel", SqlDbType.VarChar).Value = client.Tel;
cmd.Parameters.Add("@Tel2", SqlDbType.VarChar).Value = client.Tel2;
cmd.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = client.Mobile;
cmd.Parameters.Add("@Fax", SqlDbType.VarChar).Value = client.Fax;
cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = client.Email;
cmd.Parameters.Add("@Agent", SqlDbType.TinyInt).Value = client.Agent;
cmd.Parameters.Add("@CTSUsername", SqlDbType.VarChar).Value = client.CTSUsername;
cmd.Parameters.Add("@CTSPassword", SqlDbType.VarChar).Value = client.CTSPassword;
cmd.Parameters.Add("@LMeNTRY", SqlDbType.Bit).Value = client.LMeNTRY;
cmd.Parameters.Add("@FarmMatters", SqlDbType.Bit).Value = client.FarmMatters;
cmd.Parameters.Add("@ClientNotes", SqlDbType.VarChar).Value = client.ClientNotes;
cmd.Parameters.Add("@Support", SqlDbType.TinyInt).Value = client.Support;
cmd.Parameters.Add("@Licenes", SqlDbType.Int).Value = client.Licenes;
cmd.Parameters.Add("@IsBureau", SqlDbType.Bit).Value = client.IsBureau;
cmd.Parameters.Add("@OperatingSystem", SqlDbType.TinyInt).Value = client.OperatingSystem;
cmd.Parameters.Add("@SBI", SqlDbType.VarChar).Value = client.SBI;
cmd.Parameters.Add("@CustomCheck1", SqlDbType.Bit).Value = client.CustomCheck1;
cmd.Parameters.Add("@CustomCheck2", SqlDbType.Bit).Value = client.CustomCheck2;
cmd.Parameters.Add("@CustomCheck3", SqlDbType.Bit).Value = client.CustomCheck3;
cmd.Parameters.Add("@CustomCheck4", SqlDbType.Bit).Value = client.CustomCheck4;
cmd.Parameters.Add("@CustomCheck5", SqlDbType.Bit).Value = client.CustomCheck5;
cmd.Parameters.Add("@CustomCheck6", SqlDbType.Bit).Value = client.CustomCheck6;
cmd.Parameters.Add("@Identity", SqlDbType.Int);

cmd.Parameters["@Identity"].Direction = System.Data.ParameterDirection.Output;

if(isClosed) dCon.Open();
cmd.ExecuteNonQuery();
if(isClosed) dCon.Close();

return Convert.ToInt32(cmd.Parameters["@Identity"].Value);

Re: SCOPE_IDENTITY() Always Returning 1

Jon Sagara25-Jan-07 10:23

Jon Sagara

25-Jan-07 10:23

Stored procedure name as defined:

Howellsy wrote:
ALTER PROCEDURE [dbo].[InsertInvoice]

Stored procedure called by your code:

Howellsy wrote:
cmd.CommandText = "InsertClient";

Maybe that could be the problem? Or is it just a copy-past error (looks like it)?

It would be helpful to see the definition of the Invoice table.

Jon Sagara
I controlled my laughter and simple said "No,I am very busy,so I can't write any code for you". The moment they heard this all the smiling face turned into a sad looking face and one of them farted. So i had to leave the place as soon as possible.
--Mr.Prakash

Blog | Site | Articles

Re: SCOPE_IDENTITY() Always Returning 1

Farm Developer25-Jan-07 21:42

Farm Developer

25-Jan-07 21:42

It was just a copy and paste error, that is the actually Stored Procedure, which is much the same but with different data fields. Below the Stored Procedure is the sql for creating the table. Not sure what the table definition is and how to get it, could you please tell me how to get it?

Kind Regards
Paul

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author,,name>
-- Create date: <create date,,="">
-- Description: <description,,>
-- =============================================
ALTER PROCEDURE [dbo].[InsertClient]
-- Add the parameters for the stored procedure here
@HoldingNumber varchar(12)
,@TradingTitle varchar(30)
,@Surname varchar(30)
,@ContactName varchar(30)
,@Address1 varchar(30)
,@Address2 varchar(30)
,@Address3 varchar(30)
,@Address4 varchar(30)
,@Postcode varchar(30)
,@Tel varchar(20)
,@Tel2 varchar(20)
,@Mobile varchar(20)
,@Fax varchar(20)
,@Email varchar(255)
,@Agent tinyint
,@CTSUsername varchar(15)
,@CTSPassword varchar(15)
,@LMeNTRY bit
,@FarmMatters bit
,@ClientNotes varchar(255)
,@Support tinyint
,@Licenes int
,@IsBureau bit
,@OperatingSystem tinyint
,@SBI varchar(20)
,@CustomCheck1 bit
,@CustomCheck2 bit
,@CustomCheck3 bit
,@CustomCheck4 bit
,@CustomCheck5 bit
,@CustomCheck6 bit
,@Identity bit OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO [BreedSociety].[dbo].[Client]
([HoldingNumber]
,[TradingTitle]
,[Surname]
,[ContactName]
,[Address1]
,[Address2]
,[Address3]
,[Address4]
,[Postcode]
,[Tel]
,[Tel2]
,[Mobile]
,[Fax]
,[Email]
,[Agent]
,[CTSUsername]
,[CTSPassword]
,[LMeNTRY]
,[FarmMatters]
,[ClientNotes]
,[Support]
,[Licenes]
,[IsBureau]
,[OperatingSystem]
,[SBI]
,[CustomCheck1]
,[CustomCheck2]
,[CustomCheck3]
,[CustomCheck4]
,[CustomCheck5]
,[CustomCheck6])
VALUES
(@HoldingNumber
,@TradingTitle
,@Surname
,@ContactName
,@Address1
,@Address2
,@Address3
,@Address4
,@Postcode
,@Tel
,@Tel2
,@Mobile
,@Fax
,@Email
,@Agent
,@CTSUsername
,@CTSPassword
,@LMeNTRY
,@FarmMatters
,@ClientNotes
,@Support
,@Licenes
,@IsBureau
,@OperatingSystem
,@SBI
,@CustomCheck1
,@CustomCheck2
,@CustomCheck3
,@CustomCheck4
,@CustomCheck5
,@CustomCheck6)

SET @Identity = SCOPE_IDENTITY()
END

USE [BreedSociety]
GO
/****** Object: Table [dbo].[Client] Script Date: 01/26/2007 08:39:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Client](
[ClientID] [int] IDENTITY(1,1) NOT NULL,
[HoldingNumber] [varchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TradingTitle] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Surname] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ContactName] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address2] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address3] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Address4] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Postcode] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Tel] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Tel2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Mobile] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Fax] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Email] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Agent] [tinyint] NOT NULL,
[CTSUsername] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CTSPassword] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LMeNTRY] [bit] NOT NULL,
[FarmMatters] [bit] NOT NULL,
[ClientNotes] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Support] [tinyint] NOT NULL,
[Licenes] [int] NOT NULL,
[IsBureau] [bit] NOT NULL,
[OperatingSystem] [tinyint] NOT NULL,
[SBI] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CustomCheck1] [bit] NOT NULL,
[CustomCheck2] [bit] NOT NULL,
[CustomCheck3] [bit] NOT NULL,
[CustomCheck4] [bit] NOT NULL,
[CustomCheck5] [bit] NOT NULL,
[CustomCheck6] [bit] NOT NULL,
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientID] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Re: SCOPE_IDENTITY() Always Returning 1

Jon Sagara26-Jan-07 1:52

Jon Sagara

26-Jan-07 1:52

Howellsy wrote:
,@Identity bit OUTPUT

A BIT can only have a value of 1 or 0 (or NULL). Change the type to INT.

Jon Sagara
Kittens give Morbo gas.
-- Morbo

Blog | Site | Articles

Re: SCOPE_IDENTITY() Always Returning 1

Farm Developer26-Jan-07 4:09

Farm Developer

26-Jan-07 4:09

Thank you very much, what a silly mistake to make.

Problem In Multi Conditioned SQL:

Shahzad.Aslam24-Jan-07 22:14

Shahzad.Aslam

24-Jan-07 22:14

Hi i want to select the values which are not in the other table mentioned below:

SELECT DISTINCT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM TRANSACTIONS WHERE
message_type AND tran_type AND new_account AND rsp_code_rsp AND fraudrspcode NOT IN
(SELECT message_type,tran_type,new_account,rsp_code_rsp,fraudrspcode
FROM Tran_Types_Dimension)
the following error occuring:
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.

plz help this.

SHAHZAD ASLAM
Software Engineer
shehzadaslam@hotmail.com

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.