How to insert in same table sql server?

Question

I have a table named AccessRight with this sample data:

ID   Accessfor   AccessOn
---------------------------
 1     40            51
 2     40            52
 3     40            53
 4     43            51
 4     43            54
 5     43            55

I want to insert/delete records of accessfor=40 and insert this table as a accessfor=43 means table will have records like below

1     43            51
2     43            52
3     43            53
4     43            54
5     43            55

Please suggest best optimum approach to solved this.

cant you make update to Accessfor = 40 to Accessfor = 43

Amer
– Amer

2015-01-01 07:02:46 +00:00
Commented Jan 1, 2015 at 7:02 — Amer
– Amer, Commented Jan 1, 2015 at 7:02

Pரதீப் · Accepted Answer · 2015-01-01 07:03:37Z

1

use update and delete

Use CTE to delete the duplicate records after updation.

update table set Accessfor = 43 where Accessfor = 40

;with cte as
(
select row_number() over (partition by Accessfor, AccessOn order by id) rn,* 
from table
)
delete from cte where rn>1

answered Jan 1, 2015 at 7:03

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sandipk Tatva Over a year ago

Thank you.But doing above query is giving error like below.Please do the needful 'AK_UserGroupRelation_GroupIDUserID'. The duplicate key value is (43, 51)

Tassadaque Over a year ago

There seem to be constraint on table. First drop the constraint in start of query and add constraint again after delete

Sandipk Tatva Over a year ago

DBA will not allow to drop constrains,Please do the need ful.

Pரதீப் Over a year ago

@SandipkTatva - what is the problem in dropping and recreating

Hotdin Gurning · Accepted Answer · 2015-01-01 09:44:19Z

1

 Insert into AccessRight(ID,AccessFor,AccessOn)
   (select id,43,AccessOn from AccessRight where accessfor = 40 
   and accessOn not in(select accessOn from AccessRight where accessfor = 43));
   Delete from AccessRight where accessfor = 40;

EDIT : (Try this)

Insert into AccessRight(ID,AccessFor,AccessOn)
(
select id,0,AccessOn from AccessRight 
where id+''+accessfor+''+accesson not in (
select id+''+accessfor+''+accesson from accessright
where accessfor = 43
and accesson in(select accesson from accessright where accessfor = 40)
)
);
delete from accessright where accessfor <> 0;
update accessright set accessfor = 43;

edited Jan 1, 2015 at 9:44

answered Jan 1, 2015 at 7:49

Hotdin Gurning

1,8193 gold badges16 silver badges24 bronze badges

Comments

priya786 · Accepted Answer · 2015-01-01 07:07:09Z

0

you can just use update query to solve your problem like

update yourtablename set Accessfor=43 where Accessfor=40

i hope this may help you

answered Jan 1, 2015 at 7:07

priya786

1,83413 silver badges11 bronze badges

1 Comment

Sandipk Tatva Over a year ago

Then what about the duplicate entries like 43 51 and 43 51?Please advise priya786

Paresh J · Accepted Answer · 2015-01-01 08:08:20Z

What you can do is take distinct AccessOn record to temp table and truncate first table and re insert all the distinct records with Accessfor = 43.

Check this :

-- Your Main Table
Create table #temp1
(
   Accessfor int,
   AccessOn int
)

-- This will be temp table
Create table #temp2
(
   Accessfor int,
   AccessOn int
)

-- Insert into Main table. This step was for me to get data in Main table.
Insert into #temp1 values(40, 51)
Insert into #temp1 values(40, 52)
Insert into #temp1 values(40, 53)
Insert into #temp1 values(43, 51)
Insert into #temp1 values(43, 54)
Insert into #temp1 values(43, 55)

select * from #temp1

--From here actual operation starts...
Insert into #temp2
  Select Distinct '43', AccessOn From #temp1

Truncate table #temp1
Insert into #temp1
  Select * From #temp2

select * from #temp1

priya786 · Accepted Answer · 2015-01-01 08:17:47Z

0

then you can check it with the query

mysql_query("select id from tablename where  Accessfor=43 &&  Accesson='anyvalue'");
if(mysql_num_rows()>0)
{
mysql_query("delete from tablename Accessfor=43 &&  Accesson='anyvalue'");
}
else
{
//you do update
}

answered Jan 1, 2015 at 8:17

priya786

1,83413 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to insert in same table sql server?

5 Answers 5

4 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related