logo
down
shadow

Delete duplicate roles for SQL server


Delete duplicate roles for SQL server

By : Alan Railey
Date : October 14 2020, 02:22 PM
will be helpful for those in need Try this. Not super elegant and can be cleaned up but should do the trick. This will keep the first of the group. If you prefer to keep the last change to "l2.Staff_id > l1.Staff_id"
--
code :
DROP TABLE IF EXISTS LSBU_Staff

CREATE TABLE LSBU_Staff
( Staff_id          INT IDENTITY(1,1)
, LastName          VARCHAR(32)
, FirstName         VARCHAR(32)
, Speciality_type   VARCHAR(32)
, Phone_number      VARCHAR(32)
)

INSERT INTO LSBU_Staff (LastName, FirstName, Speciality_type, Phone_number)
VALUES
  ('Stilskin', 'Rumple', 'dancer'   , '305-305-3050')
, ('Lamb', 'Mary', 'shepherd'       , '305-123-4567')
, ('Lamb', 'Aurthur', 'shepherd'    , '305-123-4567')
, ('Fenokee', 'Okee', 'swimmer'     , '305-305-3051')

SELECT * FROM LSBU_Staff

DELETE LSBU_Staff 
WHERE Staff_id IN  
(
    SELECT Staff_id 
    FROM LSBU_Staff l1 
    WHERE EXISTS (SELECT 1 FROM LSBU_Staff l2 WHERE l2.Phone_number = l1.Phone_number 
                                                AND l2.Staff_id < l1.Staff_id)
)

SELECT * FROM LSBU_Staff

DROP TABLE IF EXISTS LSBU_Staff


Share : facebook icon twitter icon
Delete duplicate records in SQL Server?

Delete duplicate records in SQL Server?


By : Alex
Date : March 29 2020, 07:55 AM
this will help You can do this with window functions. It will order the dupes by empId, and delete all but the first one.
code :
delete x from (
  select *, rn=row_number() over (partition by EmployeeName order by empId)
  from Employee 
) x
where rn > 1;
select *
from (
  select *, rn=row_number() over (partition by EmployeeName order by empId)
  from Employee 
) x
where rn > 1;
Roles and system roles greyed out in SQL Server 2008R2 Management studio

Roles and system roles greyed out in SQL Server 2008R2 Management studio


By : Tiago Donatti
Date : March 29 2020, 07:55 AM
will help you is your sql server management studio running on admin login?? if not right click on it and run as admin.
How to populate user(Identity) roles for a Web application when roles are stored in a SQL Server database

How to populate user(Identity) roles for a Web application when roles are stored in a SQL Server database


By : user3412787
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , I am not sure this is a SQL Server question. This is an ASP.NET question.
ASP.NET forms authentication allows the application to define a "Principal" which (among other things) contains an array of strings known as "roles." You can populate the roles from the DB one time (when the user signs on) then serialize the principal into the forms authentication ticket, which becomes an encrypted cookie on the browser. ASP.NET decodes the cookie with each http request and provides it to your ASP.NET c# code via HttpContext.User. It can then retrieve the roles from context and never needs to talk to the DB again.
code :
string roles = "Admin,Member";
FormsAuthenticationTicket authTicket = new FormsAuthenticationTicket(
    1,
    userId,  //user id
    DateTime.Now,
    DateTime.Now.AddMinutes(20),  // expiry
    false,  //do not remember
    roles, 
    "/");
HttpCookie cookie = new HttpCookie(FormsAuthentication.FormsCookieName,
                                   FormsAuthentication.Encrypt(authTicket));
Response.Cookies.Add(cookie);
delete Duplicate Row SQL server

delete Duplicate Row SQL server


By : Daisy
Date : March 29 2020, 07:55 AM
To fix the issue you can do I am using SQL Server . i have creating SQL table have more than 20000 lines. i have Filter duplicate Using Following Query.
code :
;WITH cte
         AS (Select ROW_NUMBER() OVER  (PARTITION BY Entity,ExpenseType,Amount,Description,APSupplierID,ExpenseReportID,Employee,ExpenseDate ORDER BY ( SELECT 0)) RowNum
             FROM   TotalsByGLCenter)
    Delete FROM cte
    WHERE  RowNum > 1;
   ;WITH cte
         AS (Select ROW_NUMBER() OVER  (PARTITION BY Entity,ExpenseType,Amount,Description,APSupplierID,ExpenseReportID,Employee,ExpenseDate ORDER BY ( SELECT 0)) RowNum,Entity,ExpenseType,Amount,Description,APSupplierID,ExpenseReportID,Employee,ExpenseDate 
             FROM   TotalsByGLCenter)
    Select * FROM cte
    WHERE  RowNum > 1;
Should servers with similar roles have one role with different profiles, or different roles per server?

Should servers with similar roles have one role with different profiles, or different roles per server?


By : martin Suleman
Date : March 29 2020, 07:55 AM
it should still fix some issue Instead of directly advising you, I will describe the intent Puppetlabs has for these terms followed by an example so you will see it generally.
Modules are collections of manifests, functions, files, templates, etc. Profiles are collections of one or more modules. Roles are collection of one or more profiles. Servers are collections of one or more roles.
Related Posts Related Posts :
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • SQL - Get count of group by column but also select top item of group
  • How to add an array of datarows into an exisitng table inside my database
  • There is no unique constraint matching given keys for referenced table "employee" 1
  • SQL: Unable to SELECT joined column
  • How to find out how much space a SQL Server table uses?
  • Window function to remove specific records from SQL Server dataset
  • How to add a column for each day in sql?
  • Create group column based on the specific rows
  • Not sure if this consistitues a transitive dependency
  • How to compare the values in a column to a long list in SQL Server
  • Preserving data format Decimal(6,5) from vba to sql
  • Oracle Query to rollup QTY by Year- only last 3 years
  • SQL - Calculate 2 columns and view result to another to column
  • Divide or Multiply according to a condition (Improving query)
  • PostgreSQL unnest() with consecutive integers grouped by number
  • SQL to limit output to certain months and years
  • VARCHAR TIME TO GET THE DIFFERENCE
  • SQL conditional constraint on multiple columns being unique
  • Optimize a SQL select query in a loop
  • BTEQ Teradata Import Multiple files into one table
  • Update SQL datetime column with oldest values of another table column?
  • Is INSERT ... SELECT an atomic transaction?
  • SQL query completed successfully but not results
  • SQL sub select returning multiple values
  • Verify condition on two columns
  • SQL conditional field, first match JOIN
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org