469,616 Members | 1,699 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,616 developers. It's quick & easy.

How to update just one record


Hi

I've a table with 2 columns, one for a client code and one for a
date/time and could be more than one record with the same client code
and date/time. the 3rd column is another date/time, NULL by default.

I need to check if exists records for a determinated client code and
date/time and place the current date/time in the 3rd column for just one
and only one record.

Is this possible ? How ?

Thanks in advance

J

Jul 23 '05 #1
9 2202
Hi

If the client_code and datetime1 are the Primary Key then there will only be
one combination of the two and therefore matching a single record should not
be a problem!

If there are multiple records and you can not distinguish them then you
update one at a time then you could SET ROWCOUNT before issuing the
statement, and then set it back after.

John

"Javier" <jl****@manresa.net> wrote in message
news:cu**********@news.ya.com...

Hi

I've a table with 2 columns, one for a client code and one for a date/time
and could be more than one record with the same client code and date/time.
the 3rd column is another date/time, NULL by default.

I need to check if exists records for a determinated client code and
date/time and place the current date/time in the 3rd column for just one
and only one record.

Is this possible ? How ?

Thanks in advance

J

Jul 23 '05 #2
Ray
J,

So you have no primary key on the table. I would strongly encourage you to
rething the design of the table. However, (and this breaks the relational
rules) try setting the rowcount to 1 before and back to 0 after the update.

Ray
"Javier" <jl****@manresa.net> wrote in message
news:cu**********@news.ya.com...

Hi

I've a table with 2 columns, one for a client code and one for a date/time
and could be more than one record with the same client code and date/time.
the 3rd column is another date/time, NULL by default.

I need to check if exists records for a determinated client code and
date/time and place the current date/time in the 3rd column for just one
and only one record.

Is this possible ? How ?

Thanks in advance

J

Jul 23 '05 #3
Eliminate the duplicates and fix your table design. It's as simple as that.
Every table should have a primary key and yours has none.

--
David Portas
SQL Server MVP
--
Jul 23 '05 #4
Javier (jl****@manresa.net) writes:
I've a table with 2 columns, one for a client code and one for a
date/time and could be more than one record with the same client code
and date/time. the 3rd column is another date/time, NULL by default.

I need to check if exists records for a determinated client code and
date/time and place the current date/time in the 3rd column for just one
and only one record.

Is this possible ? How ?


SET ROWCOUNT 1
UPDATE tbl
SET col3 = getdate()
WHERE col1 = @clientcode
AND col2 = @datetime
SET ROWCOUNT 0
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

Rows are not records. Tables are not files. To be a table, you must
have a key. This is a basic definition in the relational model. Is
tgis what you meant?

CREATE TABLE ClientHistory
(client_name CHAR(15) NOT NULL,
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME,
PRIMARY KEY (client_name, start_date));

then you can write standard, portable code with any need for
proprietary syntax.

UPDATE ClientHistory
SET end_date = COALESCE (end_date, CURRENT_TIMESTAMP)
WHERE client_name = @my_guy
AND start_date = @my_date;

Jul 23 '05 #6
--CELKO-- wrote:
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

Rows are not records. Tables are not files. To be a table, you must
have a key. This is a basic definition in the relational model. Is
tgis what you meant?

CREATE TABLE ClientHistory
(client_name CHAR(15) NOT NULL,
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME,
PRIMARY KEY (client_name, start_date));

then you can write standard, portable code with any need for
proprietary syntax.

UPDATE ClientHistory
SET end_date = COALESCE (end_date, CURRENT_TIMESTAMP)
WHERE client_name = @my_guy
AND start_date = @my_date;


Thanks for your reply.

Ok, I've created the table in this way (I've added a primary key)

CREATE TABLE [control] (
[clientes_codigo] int,
[fecha] datetime,
[registro] datetime,
[controlid] uniqueidentifier DEFAULT (newid()) NOT NULL,
CONSTRAINT [control_pk] PRIMARY KEY ([controlid]),
CONSTRAINT [control_fk] FOREIGN KEY ([clientes_codigo])
REFERENCES [dbo].[clientes] ([codigo])
ON UPDATE NO ACTION
ON DELETE CASCADE
)
ON [PRIMARY]
GO

And now I've created a store procedure to make the job:

CREATE PROCEDURE setcontrol
@clientcode int = 0,
@maxdif int = 0
AS
BEGIN
SET ROWCOUNT 1
UPDATE control
SET registro = getdate()
WHERE clientes_codigo = @clientcode
AND (fecha >= getdate()
AND fecha < dateadd(mi, @maxdif, getdate())
SET ROWCOUNT 0
END
Now I want to know how could I pass parameteres when I call the store
procedure... Also, how could I return a value if the sp updated a row or
not ?

Thanks in advance
J
Jul 23 '05 #7
On 2/7/05 4:27 PM, in article cu**********@news.ya.com, "Javier"
<jl****@manresa.net> wrote:
--CELKO-- wrote:
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

Rows are not records. Tables are not files. To be a table, you must
have a key. This is a basic definition in the relational model. Is
tgis what you meant?

CREATE TABLE ClientHistory
(client_name CHAR(15) NOT NULL,
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME,
PRIMARY KEY (client_name, start_date));

then you can write standard, portable code with any need for
proprietary syntax.

UPDATE ClientHistory
SET end_date = COALESCE (end_date, CURRENT_TIMESTAMP)
WHERE client_name = @my_guy
AND start_date = @my_date;


Thanks for your reply.

Ok, I've created the table in this way (I've added a primary key)

CREATE TABLE [control] (
[clientes_codigo] int,
[fecha] datetime,
[registro] datetime,
[controlid] uniqueidentifier DEFAULT (newid()) NOT NULL,
CONSTRAINT [control_pk] PRIMARY KEY ([controlid]),
CONSTRAINT [control_fk] FOREIGN KEY ([clientes_codigo])
REFERENCES [dbo].[clientes] ([codigo])
ON UPDATE NO ACTION
ON DELETE CASCADE
)
ON [PRIMARY]
GO

And now I've created a store procedure to make the job:

CREATE PROCEDURE setcontrol
@clientcode int = 0,
@maxdif int = 0
AS
BEGIN
SET ROWCOUNT 1
UPDATE control
SET registro = getdate()
WHERE clientes_codigo = @clientcode
AND (fecha >= getdate()
AND fecha < dateadd(mi, @maxdif, getdate())
SET ROWCOUNT 0
END
Now I want to know how could I pass parameteres when I call the store
procedure... Also, how could I return a value if the sp updated a row or
not ?

Thanks in advance
J


Will you be calling the stored procedure from a VB,C++ application or a
..NET, .asp website?

The code is slightly different for each.
In Query Analyzer you just append the parameters to the stored procedure:
EXEC setcontrol 1234, 4321
Add a print statement to the stored procedure to get feedback in Query
Analyzer.
You can only return an int using a RETURN statement so perhaps a 1 for
success and 0 for failure.

IF EXISTS (SELECT * FROM [control] WHERE clientes_codigo = @clientcode AND
registro = GETDATE())
BEGIN
RETURN 1
END
ELSE
BEGIN
RETURN 0
END

I would also recommend declaring a variable to hold the date and call
GETDATE() only once in your stored procedure.

-Greg

Jul 23 '05 #8
Gregory Dean wrote:
And now I've created a store procedure to make the job:

CREATE PROCEDURE setcontrol
@clientcode int = 0,
@maxdif int = 0
AS
BEGIN
SET ROWCOUNT 1
UPDATE control
SET registro = getdate()
WHERE clientes_codigo = @clientcode
AND (fecha >= getdate()
AND fecha < dateadd(mi, @maxdif, getdate())
SET ROWCOUNT 0
END
Now I want to know how could I pass parameteres when I call the store
procedure... Also, how could I return a value if the sp updated a row or
not ?

Thanks in advance
J

Will you be calling the stored procedure from a VB,C++ application or a
.NET, .asp website?

The code is slightly different for each.
In Query Analyzer you just append the parameters to the stored procedure:
EXEC setcontrol 1234, 4321
Add a print statement to the stored procedure to get feedback in Query
Analyzer.
You can only return an int using a RETURN statement so perhaps a 1 for
success and 0 for failure.

IF EXISTS (SELECT * FROM [control] WHERE clientes_codigo = @clientcode AND
registro = GETDATE())
BEGIN
RETURN 1
END
ELSE
BEGIN
RETURN 0
END

I would also recommend declaring a variable to hold the date and call
GETDATE() only once in your stored procedure.

-Greg

Thanks for your reply.

I'll call the sp from a VC++ 6 application (using ADO). (I'm trying to
do it...is my first time...)

I understand your example about the SP but I wonder if it could be more
complete. i.e.: could I did something like this:

IF EXISTS (SELECT * FROM [control] WHERE clientes_codigo = @clientcode AND
registro = GETDATE())
BEGIN
UPDATE control
SET registro = getdate()
WHERE clientes_codigo = @clientcode
AND (fecha >= getdate()
AND fecha < dateadd(mi, @maxdif, getdate())
RETURN 1
END
ELSE
BEGIN
RETURN 0
END

I mean to include the update statement to do SP make all the job.

Also, I wonder if the upgrade statement could return something to know
if it was success or fail and I would like to know if could I lock the
record I get from the select to allow the update...
Thanks in advance

J
Jul 23 '05 #9
Javier (jl****@manresa.net) writes:

CREATE PROCEDURE setcontrol
@clientcode int = 0,
@maxdif int = 0
AS
BEGIN
SET ROWCOUNT 1
UPDATE control
SET registro = getdate()
WHERE clientes_codigo = @clientcode
AND (fecha >= getdate()
AND fecha < dateadd(mi, @maxdif, getdate())
SET ROWCOUNT 0
END
Now I want to know how could I pass parameteres when I call the store
procedure...
EXEC setcontrol 7, 7
Also, how could I return a value if the sp updated a row or not ?


CREATE PROCEDURE setcontrol
@clientcode int = 0,
@maxdif int = 0,
@rowc int OUTPUT
AS
BEGIN
SET ROWCOUNT 1
UPDATE control
SET registro = getdate()
WHERE clientes_codigo = @clientcode
AND (fecha >= getdate()
AND fecha < dateadd(mi, @maxdif, getdate())
SELECT @rowc = @@rowcount
SET ROWCOUNT 0
END
go
DECLARE @rowc int
EXEC setcontrol 8, 8, @rowc OUTPUT
SELECT @rowc
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by Philip Boonzaaier | last post: by
5 posts views Thread by PAUL | last post: by
13 posts views Thread by Neil | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.