Trying to append a field if an insert happens to c

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

SQL Server 2000 Forums

Transact-SQL (2000)

Trying to append a field if an insert happens to c

Author

Topic

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-01-09 : 01:00:55

I deliberately intend to add some duplicates to one of my tables. For eg

Job User IsAdmin

JobID 235 User ID 1
JobID 235 User ID 5
JobID 235 User ID 9
JobID 235 User ID 5
JobID 235 User ID 2
JobID 235 User ID 9
JobID 235 User ID 10
JobID 235 User ID 1

I know its bad practice to do such a thing but there is a genuine reason. What I need to do is to be able to have a SQL statement that appends true to the IsAdmin field whenever it encounters the next UserID thats happens to be a duplicate. Hence the above would look like:

Job User IsAdmin

JobID 235 User ID 1
JobID 235 User ID 5
JobID 235 User ID 9
JobID 235 User ID 5 True
JobID 235 User ID 2
JobID 235 User ID 9 True
JobID 235 User ID 10
JobID 235 User ID 1 True

Thanks

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-01-09 : 01:18:07

Can't be done, unless you assign another column which is unique. But if you do that, then its trivial..It's not that its a bad idea, its just that if they really are duplicate rows, there's no way to individually reference them, and they may as well not be there...

Also, is your table
Job,User,IsAdmin
----
JobID 235,User ID 1,
JobID 235,User ID 5,

or
Job,User,IsAdmin
----
JobID 235, User ID, 1
JobID 235, User ID, 5

Or what? Can you repost with commas?

Anyway, here's the idea...

create table jobs (jobid varchar(20), userid varchar(20), isadmin varchar(20))
go
insert into jobs (jobid, userid, isadmin) select '235','5',null
insert into jobs (jobid, userid, isadmin) select '235','7',null
insert into jobs (jobid, userid, isadmin) select '235','9',null
insert into jobs (jobid, userid, isadmin) select '235','5',null
insert into jobs (jobid, userid, isadmin) select '235','5',null
insert into jobs (jobid, userid, isadmin) select '235','7',null

select * from jobs

alter table jobs add id int identity(1,1)
go
update jobs
set isadmin = 'true'
where id in
(
select a.id
from jobs a, jobs b 
where a.id > b.id
and a.jobid = b.jobid
and a.userid = b.userid
)

select * from jobs

cheers

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-01-09 : 02:10:31

Use an identity column.

CREATE Table JobUser
(
JobUserID int IDENTITY(1, 1),
JobID int,
UserID int,
IsAdmin bit
)

This way you can do this for your stored procedure.

CREATE PROCEDURE InsertJobUser
(
@JobID int,
@UserID int
)
AS

INSERT INTO JobUser (JobID, UserID, IsAdmin)
SELECT @JobID, @UserID,
CASE WHEN @JobID = (SELECT j.JobID
FROM JobUser j
WHERE j.JobUserID = (SELECT MAX(j2.JobUserID)
FROM JobUser j2)
AND j.IsAdmin = 0)
THEN 1
ELSE 0
END
GO

This should work for you (I think).

Dustin Michaels

saglamtimur
Yak Posting Veteran

91 Posts

Posted - 2004-01-09 : 02:35:30

rrb's solution is for a table which currently has records. You can use it if you already have records. But, if I didn't misunderstand your question, you want to do an automation for future inserts (Question topic : Trying to append a field if an insert happens to ). If this is the case, you can use triggers. Here is the sample;

create trigger recdeneme on jobs instead of insert as

declare @jobid userid varchar(20), userid varchar(20)
set @jobid= (select jobid from inserted)
set @userid = (select userid from inserted)
if exists (select * from jobs where (jobid=@jobid and userid=@userid and isadmin is null))
-- we found the record, so set isadmin to true for new insert
begin
insert into jobs (jobid, userid, isadmin) values
(@jobid, @userid, 'true')
end
else
-- This is the first time of this record inserted, so do nothing just insert
begin
insert into job (jobid, userid) values
(@jobid, @userid)
end

But what about Nth same value insert? In this case this trigger will set isadmin to true for the same recuring records.

Home this helps.

g_r_a_robinson
Starting Member

45 Posts

Posted - 2004-01-09 : 19:01:48

The last reply was exactly what I needed. As to some of the other questions:

rrb : Can't be done, unless you assign another column which is unique. But if you do that, then its trivial..It's not that its a bad idea, its just that if they really are duplicate rows, there's no way to individually reference them, and they may as well not be there...

May I ask a question regarding this? I understand that I won't be able to reference two ids with the same id but can I not differentiate two rows with the same id if for eg the admin field is set to true in one and not the other. eg

Job User Admin
jobID 5, userID 4, True
jobID 5, userID 4, Null

so i could sael, select jobid(my jobiD) where Admin = true. Would that not work. There will never be more than two rows that will have exactly the same data, IE one will be admin one will not. Will that work?

rrb:Also, is your table
Job,User,IsAdmin
----
JobID 235,User ID 1,
JobID 235,User ID 5,

or
Job,User,IsAdmin
----
JobID 235, User ID, 1
JobID 235, User ID, 5

Or what? Can you repost with commas?

Sorry its actually

Job,User,IsAdmin
----------------

235, 3, True
236, 7, Null
236, 7, True

Hope that makes sense.

saglamtimur : But what about Nth same value insert? In this case this trigger will set isadmin to true for the same recuring records.

There will only ever be one duplicate record. I make sure of that.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-01-11 : 17:01:37

>so i could sael, select jobid(my jobiD) where Admin = true.

yep that will work, but since the rows are now no longer identical, you may as well have a hidden identity which is not unique.

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources