Começando com SQL Server na prática - Parte 2 (Consulta avançada, edição e remoção de dados)
O UPDATE no SQL Server é o comando que edita registros já gravados, e ele aparece aqui ao lado de consultas com junções, views, alterações de colunas e remoção de dados. Este artigo continua o projeto prático de eventos e mostra, passo a passo, como manipular e excluir informações no Microsoft SQL Server.
Se você está começando agora, leia antes a primeira parte da série, sobre estrutura, inserção e consulta. Caso já conheça aquelas instruções, siga em frente, porque esta é a parte final do projeto.
Onde paramos na primeira parte
Na primeira parte, você criou o banco events com as tabelas de categoria, grupo, usuário, evento e a tabela de ligação entre usuário e grupo. Além disso, cadastrou uma categoria, um grupo de palestrantes e um usuário.
Também associou esse usuário ao grupo. Agora, falta concluir os registros com a criação de um evento, que depende da categoria e do organizador.
Com o INSERT e o SELECT da primeira parte, você já cobre a criação e a leitura dos dados. Nesta parte, o UPDATE no SQL Server e o DELETE completam o ciclo, permitindo editar e remover informações.
Esse ciclo costuma ser chamado de CRUD, sigla para criar, ler, atualizar e excluir. Na prática, quase toda aplicação que usa banco de dados executa essas quatro operações o tempo todo.
Cadastrando o evento
O código abaixo grava o evento no banco:
BEGIN TRANSACTION
INSERT INTO [event] VALUES (
NEWID(),
'Persisting data',
'A detailed look at the persistence process.',
'6ccbe492-8c8c-40fc-8039-c3759b9ab69b',
GETDATE(),
GETDATE() + 7,
'e83848d3-cffd-4445-bf7a-2996583e834a',
0,
NULL
)
COMMIT
A sequência dos valores segue a ordem das colunas na tabela event. Por isso, o terceiro valor é o Id da categoria e o sexto é o Id do organizador.
Repare também na data do evento, ou seja, o dia em que ele acontece. O script pega a data atual com GETDATE() e soma 7 dias.
Somar um número inteiro funciona porque a coluna é do tipo DATETIME. Ainda assim, DATEADD(DAY, 7, GETDATE()) deixa a intenção mais clara e também funciona com DATETIME2.
Lembre-se de trocar os GUIDs do exemplo pelos Ids gerados no seu banco. Caso contrário, as chaves estrangeiras vão rejeitar o registro.
O último valor, NULL, corresponde à coluna Link, que ainda não tem conteúdo. Mais adiante, você vai preencher ou corrigir dados como esse com o UPDATE no SQL Server, sem precisar apagar e inserir o registro de novo.
Consultas com INNER JOIN e aliases
Agora, confira se as informações gravadas estão corretas. Desta vez, a consulta traz apenas alguns campos e renomeia as colunas para facilitar a leitura do resultado:
SELECT TOP (1)
[event].[Id] AS [Event Id],
[event].[Title] AS [Event],
[event].[CategoryId] AS [Category Id],
[category].[Name] AS [Category],
[event].[EventDate] AS [Event Date],
[event].[OrganizerId] AS [Organizer Id],
[user].[FullName] AS [Organizer]
FROM [events].[dbo].[event]
INNER JOIN [category] ON [category].[Id] = [event].[CategoryId]
INNER JOIN [user] ON [user].[Id] = [event].[OrganizerId]
WHERE [Title] LIKE '%data'
O operador AS dá um nome amigável a cada coluna do resultado. Já cada INNER JOIN traz os dados da categoria e do organizador usando as chaves estrangeiras.
O INNER JOIN retorna apenas as linhas que têm correspondência nas duas tabelas. Como as chaves estrangeiras obrigam todo evento a ter categoria e organizador, nenhum evento fica de fora do resultado.
Os aliases entre colchetes permitem usar espaços no nome da coluna, como em [Event Id]. Assim, o resultado fica mais legível para quem lê o relatório.
No WHERE, o sinal de porcentagem aparece apenas antes da palavra-chave. Dessa forma, a consulta retorna somente eventos cujo título termina com a palavra data.
Até aqui, você já sabe escolher campos, renomear colunas e usar o INNER JOIN. Para ver outros recursos de consulta, como subqueries e funções de janela, confira o artigo consultas SQL avançadas com exemplos.
Criando uma view no SQL Server
Escrever essa consulta inteira toda vez seria cansativo. Por isso, o SQL Server permite salvar a consulta como uma VIEW, uma tabela virtual que você consulta passando apenas o filtro desejado.
O script de criação fica assim:
CREATE VIEW [searchEvent] AS
SELECT
[event].[Id] AS [Event Id],
[event].[Title] AS [Event],
[event].[CategoryId] AS [Category Id],
[category].[Name] AS [Category],
[event].[EventDate] AS [Event Date],
[event].[OrganizerId] AS [Organizer Id],
[user].[FullName] AS [Organizer]
FROM [events].[dbo].[event]
INNER JOIN [category] ON [category].[Id] = [event].[CategoryId]
INNER JOIN [user] ON [user].[Id] = [event].[OrganizerId]
Basta acrescentar a linha CREATE VIEW [name] AS antes do SELECT e remover o WHERE. Assim, o padrão de visualização fica salvo no banco.
Repare que o TOP (1) também saiu da view. Afinal, o SQL Server aplicaria o TOP dentro da view antes do filtro externo, e a busca poderia não encontrar o evento procurado.
A documentação do CREATE VIEW reforça esse ponto. O ORDER BY só vale em uma view junto com TOP ou OFFSET, e mesmo assim não garante a ordem do resultado final.
Consultando a view
Para consultar os dados, em vez de repetir a consulta inteira, chame a view e acrescente a condição:
SELECT * FROM [searchEvent] WHERE [Event] LIKE '%data'
O resultado é o mesmo da consulta anterior. Note que o filtro usa o nome da coluna definido na view, [Event], e não o nome original [Title].
Onde a view fica armazenada
A view fica guardada no próprio banco, como um objeto do esquema dbo. No explorador de objetos do editor, ela aparece na pasta Views, logo abaixo da pasta Tables.
Vale lembrar que a view guarda apenas a definição da consulta, não os dados. Portanto, cada chamada executa a consulta de novo sobre as tabelas originais.
Para mudar a definição depois, use o CREATE OR ALTER VIEW, disponível a partir do SQL Server 2016 SP1. Ele cria a view se ela não existir e a atualiza caso contrário.
Também é possível rodar um UPDATE no SQL Server através de uma view. Contudo, a alteração precisa atingir colunas de uma única tabela base, regra que a documentação chama de view atualizável.
Editando registros com UPDATE no SQL Server
Imagine que você precisa mudar a descrição de um evento. Nesse caso, o comando certo é o UPDATE no SQL Server, que altera os valores de colunas em linhas existentes.
O exemplo abaixo edita a descrição do evento cadastrado:
BEGIN TRANSACTION
UPDATE [event]
SET
[Description] = 'Description pending'
WHERE [Title] LIKE '%data'
ROLLBACK
O SET define a coluna e o novo valor. Já o WHERE decide quais linhas o UPDATE no SQL Server vai alterar.
UPDATE no SQL Server dentro de transações
O script termina com ROLLBACK de propósito. Assim, o UPDATE no SQL Server roda como teste, e os dados voltam ao valor anterior assim que a transação termina.
Quando o resultado estiver correto, troque o ROLLBACK por COMMIT para aplicar a mudança. Segundo a documentação do ROLLBACK TRANSACTION, o comando desfaz tudo até o BEGIN TRANSACTION, e não há volta depois de um COMMIT.
Uma boa técnica é rodar um SELECT com o mesmo WHERE antes do UPDATE no SQL Server. Dessa forma, você confere exatamente quais linhas serão afetadas.
O WHERE no UPDATE no SQL Server
Esquecer o WHERE é o erro mais perigoso de qualquer comando de edição. Sem ele, o UPDATE no SQL Server altera todas as linhas da tabela.
Além disso, prefira filtrar pela chave primária sempre que possível. Um filtro com LIKE pode atingir mais registros do que você imagina, enquanto o Id identifica uma única linha.
Depois de executar o comando, a função @@ROWCOUNT informa quantas linhas foram afetadas. Então, compare esse número com o que você esperava antes de confirmar a transação.
Atualizando várias colunas com UPDATE no SQL Server
Um único UPDATE no SQL Server pode alterar várias colunas ao mesmo tempo. Para isso, separe as atribuições por vírgula dentro do SET:
BEGIN TRANSACTION
-- Atualiza descrição e certificado do mesmo evento
UPDATE [event]
SET
[Description] = 'Hands-on session about data persistence',
[Certificate] = 1
WHERE [Id] = '<event-id>'
-- Mostra quantas linhas o comando alterou
SELECT @@ROWCOUNT AS [AffectedRows]
ROLLBACK
Nesse exemplo, o filtro usa o Id do evento, que você obtém pela view criada antes. Assim, apenas uma linha muda.
O T-SQL também aceita uma cláusula FROM no UPDATE no SQL Server, o que permite filtrar com base em outra tabela. Por exemplo, o script abaixo marca com certificado todos os eventos de uma categoria:
BEGIN TRANSACTION
-- Atualiza os eventos usando o nome da categoria como filtro
UPDATE [event]
SET [Certificate] = 1
FROM [event]
INNER JOIN [category] ON [category].[Id] = [event].[CategoryId]
WHERE [category].[Name] = 'Lecture'
ROLLBACK
Dessa forma, você não precisa descobrir o Id da categoria antes. O INNER JOIN dentro do UPDATE no SQL Server resolve a relação na própria instrução.
Alterando colunas com ALTER TABLE
Agora, imagine que o campo de descrição deve se tornar obrigatório na tabela de eventos. Para mudar a estrutura de uma tabela, você usa o ALTER TABLE:
BEGIN TRANSACTION
ALTER TABLE [event]
ALTER COLUMN [Description] VARCHAR(150) NOT NULL;
ROLLBACK
Com esse comando, o número máximo de caracteres da descrição cai de 200 para 150. Ao mesmo tempo, o NOT NULL torna o preenchimento obrigatório.
A documentação do ALTER TABLE traz duas regras importantes aqui. Primeiro, ao usar NULL ou NOT NULL no ALTER COLUMN, você precisa repetir o tipo da coluna.
Segundo, se já existirem dados maiores que o novo tamanho, o comando falha. Isso protege você contra o corte silencioso de informações.
UPDATE no SQL Server antes do NOT NULL
O NOT NULL só é aceito se a coluna não tiver nenhum valor nulo. Por isso, o caminho seguro é rodar um UPDATE no SQL Server preenchendo os nulos antes de alterar a coluna:
BEGIN TRANSACTION
-- Preenche descrições vazias antes de exigir o campo
UPDATE [event]
SET [Description] = 'Description pending'
WHERE [Description] IS NULL;
ALTER TABLE [event]
ALTER COLUMN [Description] VARCHAR(150) NOT NULL;
ROLLBACK
Esse é o mesmo padrão que a documentação oficial recomenda. Com isso, o UPDATE no SQL Server e o ALTER TABLE trabalham juntos dentro de uma única transação.
Removendo e adicionando colunas
Para remover uma coluna da tabela, use o DROP COLUMN:
BEGIN TRANSACTION
ALTER TABLE [event]
DROP COLUMN [Link];
ROLLBACK
Esse comando retira a coluna Link da tabela de eventos, junto com todos os dados dela. Por isso, confirme que nenhuma view ou consulta depende da coluna antes do COMMIT.
Para adicionar uma coluna, use o ADD:
BEGIN TRANSACTION
ALTER TABLE [event]
ADD [Link] VARCHAR(255) NULL;
ROLLBACK
Assim, o campo Link volta a existir na tabela. Como ele aceita nulos e não tem valor padrão, as linhas existentes recebem NULL, e a operação altera apenas os metadados.
Se a nova coluna fosse NOT NULL em uma tabela com dados, seria preciso informar um DEFAULT. Nesse caso, o SQL Server preenche as linhas existentes com o valor padrão.
Em tabelas grandes, essas mudanças pedem cuidado extra. Segundo a documentação, o ALTER TABLE adquire um bloqueio de modificação de esquema, que impede outras conexões de acessar a tabela durante a alteração.
Além disso, operações que afetam todas as linhas, como remover uma coluna, podem demorar e gerar muitos registros de log. Por isso, trate essas alterações com o mesmo cuidado de um UPDATE no SQL Server que atinge milhares de linhas.
Removendo dados com DELETE
Depois de criar, consultar e editar, falta remover informações. Para apagar registros, o comando é o DELETE, que segue a mesma lógica de filtro do UPDATE no SQL Server.
BEGIN TRANSACTION
-- Remove apenas o evento informado
DELETE FROM [event]
WHERE [Id] = '<event-id>'
ROLLBACK
Assim como no UPDATE, sem WHERE o DELETE remove todas as linhas da tabela. Portanto, teste sempre dentro de uma transação e confira o @@ROWCOUNT.
Outra opção útil é a cláusula OUTPUT, que devolve as linhas removidas como resultado da própria instrução. Ela também funciona com o UPDATE no SQL Server e ajuda a registrar o que mudou:
BEGIN TRANSACTION
-- Exibe o Id e o título de cada evento removido
DELETE FROM [event]
OUTPUT [deleted].[Id], [deleted].[Title]
WHERE [Title] LIKE '%data'
ROLLBACK
Com isso, você vê exatamente quais registros saíram da tabela antes de decidir entre o COMMIT e o ROLLBACK.
As chaves estrangeiras também entram em jogo aqui. Por exemplo, você não consegue apagar um usuário que ainda organiza eventos, porque o SQL Server bloqueia a remoção para manter a integridade.
DELETE, TRUNCATE e DROP
Os três comandos removem coisas diferentes. O DELETE apaga linhas, o TRUNCATE TABLE esvazia a tabela inteira e o DROP remove o objeto do banco.
Segundo a documentação do DELETE, o TRUNCATE TABLE é mais rápido para apagar todas as linhas, porque usa menos recursos de log. No entanto, ele tem restrições, como não funcionar em tabelas referenciadas por chaves estrangeiras.
Para remover a view e uma tabela, a sintaxe com IF EXISTS evita erros quando o objeto não existe:
-- Remove a view criada neste artigo
DROP VIEW IF EXISTS [searchEvent];
-- A tabela que referencia vem antes da tabela referenciada
DROP TABLE IF EXISTS [userGroup];
O DROP TABLE não remove uma tabela que outra tabela ainda referencia por chave estrangeira. Por isso, apague primeiro as tabelas dependentes ou as constraints.
Por fim, o DROP DATABASE remove o banco inteiro. Antes, porém, troque para o banco master com USE [master], já que o SQL Server não remove um banco em uso pela sua própria conexão.
Conclusão sobre o UPDATE no SQL Server
Com esta segunda parte, o projeto de eventos fica completo. Você cadastrou o evento, combinou tabelas com INNER JOIN e salvou a consulta em uma view.
Depois, usou o UPDATE no SQL Server para editar registros, sempre dentro de transações. Também alterou colunas com ALTER TABLE e removeu dados e objetos com DELETE e DROP.
A regra que atravessa todos esses comandos é simples: teste com ROLLBACK, confira as linhas afetadas e só então use o COMMIT. Para aprofundar cada instrução, o curso Fundamentos do SQL Server, do balta.io, detalha os conceitos vistos nesta série.
FAQ
O que acontece se eu rodar um UPDATE sem WHERE?
O comando altera todas as linhas da tabela. Se ele estiver dentro de uma transação aberta, um ROLLBACK ainda desfaz a mudança.
Uma view guarda os dados da consulta?
Não, a view comum guarda apenas a definição do SELECT. Cada consulta à view executa novamente a leitura nas tabelas originais.
Por que o ALTER COLUMN com NOT NULL falha?
Ele falha quando a coluna ainda tem valores nulos. Preencha esses valores com um UPDATE antes de alterar a coluna.
Qual a diferença entre DELETE e TRUNCATE TABLE?
O DELETE remove linhas com filtro e registra cada exclusão no log. O TRUNCATE TABLE esvazia a tabela inteira de forma mais rápida, mas tem mais restrições.
Posso usar TOP dentro de uma view?
Pode, mas o TOP é aplicado antes de qualquer filtro feito na consulta externa. Por isso, ele raramente é uma boa ideia em views usadas para busca.