It's a simple code to searching by duplicate contacts on this sample code was used e-mail to looking for duplicate registers therefore if, perhaps, do you need use another field like userid, easily you can change email_address to userid, and Everything That Happens Will Happen
WARNING: The best way to do: inactive contacts.
jmayer remember us ca_contact it's one table and the registers has many relations where can to result: orphan records.
01. TO run the script
DECLARE | | @email | VARCHAR(240) |
| | , | @uuid | binary(16) |
DECLARE db_cursor CURSOR FOR
select email_address
| | from ca_contact |
| | where inactive = 0 |
| | group by email_address |
| | having count(0) >1 |
DECLARE @tmp TABLE
(
id | | int identity(1,1), |
uuid | | binary(16), |
email | | varchar(240), |
load | | varchar(8000) |
)
insert into @tmp (load) values ('TABLE ca_contact')
insert into @tmp (load) values ('id email_address inactive')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @email
WHILE @@FETCH_STATUS = 0
BEGIN
| |
| | |
| | select @uuid = max(contact_uuid) from ca_contact where email_address = @email |
| | --select @uuid,* | from ca_contact where email_address = @email |
| | | |
| | if @uuid is not null insert into @tmp (uuid, email) values(@uuid, @email) | |
FETCH NEXT FROM db_cursor INTO @email
END
CLOSE db_cursor
DEALLOCATE db_cursor
update @tmp
set load = '{"' + convert(varchar(32), uuid, 2) + '", "' + email + '", "1"}'
where uuid is not null
select load
from @tmp as tmp
order by id
02. Using exit - copy to text file called myupdate.txt - and save like UTF-8
03. Run command
pdm_load -v -u -f myupdate.txt