Zmiana numeracji id w wielu tabelach

0

Witam.
Mam takie polecenie:

  1. Planowana jest zmiana struktury organizacyjnej w firmie. Napisz program w języku Transact-SQL, który przeprowadzi odpowiednie modyfikacje. Zmieniony zostanie system numeracji działów z 10, 20, 30... na 1, 2, 3... Jeżeli dwa działy mają tą samą lokalizację, to zostaną one połączone, a dział powstały w wyniku połączenia będzie miał numer taki, jak mniejszy z istniejących numerów (Uwaga: W bazie nie ma działów o tej samej lokalizacji. Aby przetestować program, dodaj takie działy). Pensje wszystkich pracowników zostaną zmodyfikowane. Każdy z pracowników będzie zarabiał tyle, ile wynosi średnia w jego dziale (po reorganizacji) na jego stanowisku.

Na razie chcę zrobić 1 część zadania czyli:

Zmieniony zostanie system numeracji działów z 10, 20, 30... na 1, 2, 3...

Mój kod:

CREATE PROCEDURE zmiana_struktury
AS BEGIN
	DECLARE kursor_dept CURSOR FOR select deptno, dname, loc from DEPT
	DECLARE @id int, @deptno int
	DECLARE @dname varchar(30), @loc varchar(30)
	SET @id = 1
	OPEN kursor
		FETCH NEXT FROM kursor INTO @deptno, @dname, @loc
		WHILE @@FETCH_STATUS = 0
		BEGIN
			UPDATE dept
			SET deptno = @id,
			@id = @id + 1
			WHERE dname = @dname
		END
	CLOSE kursor_dept
	
	DECLARE kursor_emp CURSOR FOR select empno, deptno from EMP
		--?
	DEALLOCATE kursor
END 

Problem jest w tym, że wiem jak zmienić deptno w tabeli DEPT, ale jak to zrobić w tabeli EMP ? Myślałem o CASE np.

CASE deptno
	       WHEN 10 THEN 1
	       WHEN  20 THEN 2
               .........

Jednak to ma tylko sens dla małej ilości deptno. Jakieś pomysły, sugestie? W załącznikach są screeny dwóch tabel EMP i DEPT.
Będę wdzięczny.

4

nie wiem jaki masz cel w tym, aby wszędzie używać kursorów.
jeżeli musisz zamienić 10>1, 20>2 itd. to można zrobić to dużo prościej, szybciej i wydajniej bez używania zmiennych, kursorów, cas-ów itp.

update emp set deptno=convert(int, deptno/10)
update dept set deptno=convert(int, deptno/10)
0

Dzięki za pomoc. Sam nie wiem dlaczego ciągle kombinuje z kursorami :). Teraz mam kolejne pytanie(nie wiem czy można w tym samym temacie).
Kod:

ALTER PROCEDURE zmiana_struktury
AS BEGIN
	DECLARE kursor CURSOR FOR select deptno, dname, loc from DEPT
	DECLARE @id int, @deptno int, @deptno_loc int, @sal_loc int
	DECLARE @dname varchar(30), @loc varchar(30)
	--zmaiana systemu numeracji działów z 10, 20, 30... na 1, 2, 3...
	IF EXISTS(SELECT deptno from DEPT where deptno >= 10)
	BEGIN
	UPDATE emp SET deptno=CONVERT(INT, deptno/10)
	UPDATE dept SET deptno=CONVERT(INT, deptno/10)
	END

	OPEN kursor
		FETCH NEXT FROM kursor INTO @deptno, @dname, @loc
		--wyświetlenie lokalizacji, która występuje więcej niż jeden raz
		SET @loc = (select loc from dept where loc= @loc group by loc having count(loc) > 1)
		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF (count(@loc) != 0)
			BEGIN
				SET @deptno_loc = (select min(deptno) from dept where loc = @loc)
				SET @sal_loc = (select avg(sal) from EMP where deptno = @deptno_loc group by deptno) 

				UPDATE DEPT
				SET DEPTNO = @deptno_loc
				WHERE DEPTNO = @deptno

				UPDATE EMP
				SET DEPTNO = @deptno_loc
				WHERE DEPTNO = @deptno

				UPDATE EMP
				SET SAL = @sal_loc
				WHERE deptno=@deptno_loc
			END
			FETCH NEXT FROM kursor INTO @deptno, @dname, @loc
		END

	CLOSE kursor
	DEALLOCATE kursor
END

Czy ten skrypt jest zrobiony poprawnie? Gdy mamy działy z tą samą lokalizacją to zmienia ich deptno na minimalne. Lecz nie wiem czy chodzi o to, bo teraz będą przypadki np. wielu tych samych deptno.

1

Wg mnie nie, ponieważ nie potrzebnie (a jeżeli masz założony klucz dept.deptno to ci błąd wyrzuci) robisz update na emp, a w dept rekordy oprócz minimalnego id dla lokalizacji powinieneś usunąć.
No i znowu kursory??

Przecież można update:

--pobieramy listę nowych deptno
Select min(deptno) id, loc from dept group by loc
--pobieramy listę starych deptno 
Select deptno, loc from dept
--teraz wykorzystujemy to do podmiany starego deptno na nowe
Update emp set depno=d.id from emp e inner join (select o.deptno, n.id from ( Select min(deptno) id, loc from dept group by loc) n inner join ( Select deptno, loc from dept) o on (n.loc=o.loc) where o.deptno<>n.id) d on (e.deptno=d.deptno)
--usuwamy niepotrzebne dept
Delete from dept where deptno in (select o.deptno from ( Select min(deptno) id, loc from dept group by loc) n inner join ( Select deptno, loc from dept) o on (n.loc=o.loc) where o.deptno<>n.id)
--pobieramy średnie wypłaty dla dzialu
select deptno, avg(sal) sal from emp group by deptno
--i zmieniamy wysokość wpłaty
Update emp set sal=s.sal from emp e inner join (select deptno, avg(sal) sal from emp group by deptno) s on (e.deptno=s.deptno) 
1
Lukis92 napisał(a):

Dzięki za pomoc. Sam nie wiem dlaczego ciągle kombinuje z kursorami :).

Bo myślisz imperatywnie, tak jakbyś pisał w zwykłym języku programowania, typu C, Java czy Pascal. Tymczasem SQL to język operujący na całych zbiorach danych, więc w celu wykonania jakiejś operacji na wielu elementach nie trzeba tego robić w pętli, jak w typowych językach programowania.

1 użytkowników online, w tym zalogowanych: 0, gości: 1