Insert, Update and Delete query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert, Update and Delete query

Author

Topic

hellohello
Starting Member

12 Posts

Posted - 2011-06-15 : 04:52:50

helloooo

just wondering can i user the three of these in the same query.
I need to delete a old user_id, insert a new user_id and then update it when i run this query.

here is what i have:

ALTER PROCEDURE exampleProc(@intUserID integer,@intUserName integer)
as

begin

delete from table(user_id)

insert into table
(user_id,name,commit_state,progress,hashed_password,last_upload_time,last_download_time)
values
(@intUserID,@intUserName,0,0,null,'1900/01/01 00:00:00','1900/01/01 00:00:00')
select ReturnCode=0

update table
set user_id = @intUserID
where name = @intUserName

end

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-15 : 05:15:56

s.b. delete table where user_id = @intUserID (I hope)

But why are you doing this?
Are these all the same table? Why do you have the final update statement? It would mean that neither user_id or name is unique which doesn't make sense.

I would have thought it should be an insert if it's not there or an update if it is - on the user_id. That would be a single statement.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-15 : 12:55:29

>> Just wondering can I user the three of these in the same query. I need to delete a old user_id, insert a new user_id and then update it when I run this query. <<

Yes, you can. You should not. Read a book on Software Engineering> Look at the concept of cohesion – the requirement that a module of code do one and only one thing. This nightmare of performance and maintenance is called a “Automobiles_and_Squid_and Lady_GaGa' module.

Here is a quick clean up to get you close to ISO-11179. Please stop putting audit data in the table being audit

CREATE PROCEDURE Automobiles_and_Squid_and Lady_GaGa
(@in_user_id INTEGER, @in_user_name INTEGER)
AS
BEGIN
DELETE FROM Foobar(user_id);

INSERT INTO Foobar
(user_id, user_name, commit_state, something_progress, hashed_password, last_upload_time, last_download_time)
VALUES
(@in_user_id, @in_user_name, 0, 0, NULL, '1900-01-01', '1900-01-01')

SET returncode = 0; ---silly assembly language flag!

UPDATE Foobar
SET user_id = @in_user_id
WHERE user_name = @in_user_name;

END;

The way to do this is with a MERGE statement

MERGE INTO Foobar
USING (SELECT *
FROM (VALUES (@in_user_id, @in_user_name))
AS X (user_id, user_name)
ON Foobar.in_user_id = X.user_id
WHEN MATCHED
THEN UPDATE
SET user_name = @in_user_name
WHEN NOT MATCHED
THEN INSERT
VALUES (@in_user_id, @in_user_name,
0, 0, NULL,
CAST (CURRENT_TIMESTAMP AS DATE),
CAST (CURRENT_TIMESTAMP AS DATE));

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources