Update a partir de uma outra tabela

[clovis.sardinha 1]

Boa noite.

Tenho um bd com 2 tabelas. A primeira (serv_os) tem as colunas id, fk_os e fk_servico  a segunda (prod_os) tem as colunas id, fk_os e agora eu criei a coluna fk_serv. A coluna fk_serv vai ser preenchida com o valor id da primeira coluna. Fiz o seguinte update entre as tabelas: 

UPDATE prod_os
JOIN serv_os ON serv_os.fk_os= prod_os.fk_os
SET prod_os.fk_serv = serv_os.id
WHERE prod_os.fk_os=serv_os.fk_os;

 

Até aqui tudo bem, porém, fiquei com o seguinte problema.  Muitas vezes  existem vários serviços na primeira tabela, por exemplo id=100 produto A, id=101 produto B, porém o fk_os é o mesmo . Ai na coluna fk_serv aparece sempre o id do produto A. Nesse caso duas vezes. O produto B, C,...etc não aparece. 

Não consegui nenhum comando, já que o join é pela coluna fk_os, que fizesse o update mudar o ponteiro para o serviço B,C,etc.

Se alguém puder me ajudar eu agradeço.

[Jack Oliveira 1]

1 Etapa: Atualizar com o Serviço Mais Recente

Se você quiser atualizar prod_os.fk_serv com o id do serviço mais recente para cada fk_os, você pode usar um UPDATE com uma subconsulta que escolhe o serviço mais recente com base em algum critério, como o id mais alto. Aqui está um exemplo:

 

UPDATE prod_os
JOIN (
    SELECT fk_os, MAX(id) AS max_serv_id
    FROM serv_os
    GROUP BY fk_os
) AS latest_serv ON prod_os.fk_os = latest_serv.fk_os
SET prod_os.fk_serv = latest_serv.max_serv_id;

2 Etapa: Atualizar com o Primeiro Serviço Encontrado

Se a ideia é atualizar prod_os.fk_serv com o id do primeiro serviço encontrado (por exemplo, o serviço com o menor id), você pode fazer isso:

 

UPDATE prod_os
JOIN (
    SELECT fk_os, MIN(id) AS min_serv_id
    FROM serv_os
    GROUP BY fk_os
) AS first_serv ON prod_os.fk_os = first_serv.fk_os
SET prod_os.fk_serv = first_serv.min_serv_id;

3 Etapa: Atualizar prod_os.fk_serv com Base em um Critério Específico

Se você tiver um critério específico para escolher entre os serviços (por exemplo, o serviço com o maior id ou o menor id), você pode ajustar a subconsulta conforme necessário. Aqui estão alguns exemplos:

Maior id de serv_os para cada fk_os

 

UPDATE prod_os
JOIN (
    SELECT fk_os, MAX(id) AS max_serv_id
    FROM serv_os
    GROUP BY fk_os
) AS selected_serv ON prod_os.fk_os = selected_serv.fk_os
SET prod_os.fk_serv = selected_serv.max_serv_id;

Menor id de serv_os para cada fk_os

 

UPDATE prod_os
JOIN (
    SELECT fk_os, MIN(id) AS min_serv_id
    FROM serv_os
    GROUP BY fk_os
) AS selected_serv ON prod_os.fk_os = selected_serv.fk_os
SET prod_os.fk_serv = selected_serv.min_serv_id;

4 Etapa: Atualizar prod_os.fk_serv para um serviço específico baseado em alguma condição

Se você tiver uma condição específica para escolher um serviço, você pode adicionar uma condição WHERE na subconsulta. Por exemplo, se você quiser o serviço que tem uma descrição específica:

 

UPDATE prod_os
JOIN (
    SELECT s.fk_os, s.id AS serv_id
    FROM serv_os s
    JOIN (
        SELECT fk_os, MIN(id) AS min_serv_id
        FROM serv_os
        WHERE descricao = 'Serviço X'
        GROUP BY fk_os
    ) AS x ON s.fk_os = x.fk_os AND s.id = x.min_serv_id
) AS specific_serv ON prod_os.fk_os = specific_serv.fk_os
SET prod_os.fk_serv = specific_serv.serv_id;

 

5 Etapa: Resolução de conflitos e verificação

Depois de executar a atualização, você pode querer verificar se todos os prod_os.fk_serv foram atualizados corretamente:

 

SELECT p.fk_os, COUNT(*) as count_serv
FROM prod_os p
JOIN serv_os s ON p.fk_os = s.fk_os
GROUP BY p.fk_os;

 

Esse comando conta quantos serviços existem para cada fk_os. Certifique-se de que cada fk_os está referenciando um serviço único, conforme a lógica do seu UPDATE.

 

Exemplo final

Aqui está um exemplo completo, escolhendo o serviço com o menor id para cada fk_os:

 

UPDATE prod_os
JOIN (
    SELECT fk_os, MIN(id) AS min_serv_id
    FROM serv_os
    GROUP BY fk_os
) AS selected_serv ON prod_os.fk_os = selected_serv.fk_os
SET prod_os.fk_serv = selected_serv.min_serv_id;

 

Agora vamos montar a Estrutura do Script PHP com OOP e PDO

Aqui está um exemplo completo que cobre a conexão com o banco de dados, a execução das consultas SQL, e a verificação dos resultados usando PDO e uma abordagem orientada a objetos.

1. Criar a Classe de Conexão

Primeiro, você cria uma classe para gerenciar a conexão com o banco de dados e executar as consultas. por exemplo

Database.php

 

<?php
class Database {
    private $host = 'localhost';
    private $db_name = 'bancodedados';
    private $username = 'usuario';
    private $password = 'senha';
    public $conn;

    public function __construct() {
        $this->connect();
    }

    private function connect() {
        $dsn = "mysql:host={$this->host};dbname={$this->db_name};charset=utf8";
        try {
            $this->conn = new PDO($dsn, $this->username, $this->password);
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            echo "Connected successfully<br>";
        } catch (PDOException $e) {
            echo "Connection failed: " . $e->getMessage();
            exit;
        }
    }

    public function query($sql, $params = [], $fetch = false) {
        $stmt = $this->conn->prepare($sql);
        $stmt->execute($params);
        if ($fetch) {
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        }
        return $stmt;
    }
}
?>

2 Atualizar prod_os.fk_serv com o Serviço Mais Recente

Este método atualiza a coluna fk_serv com o id do serviço mais recente para cada fk_os.

servico_mais_recente.php

 

<?php
require_once('Database.php');

$db = new Database();

// Atualiza fk_serv com o id do serviço mais recente para cada fk_os
$sql = "
    UPDATE prod_os
    JOIN (
        SELECT fk_os, MAX(id) AS max_serv_id
        FROM serv_os
        GROUP BY fk_os
    ) AS latest_serv ON prod_os.fk_os = latest_serv.fk_os
    SET prod_os.fk_serv = latest_serv.max_serv_id;
";

try {
    $db->query($sql);
    echo "Atualização com o serviço mais recente realizada com sucesso.<br>";
} catch (Exception $e) {
    echo "Erro na atualização com o serviço mais recente: " . $e->getMessage() . "<br>";
}
?>

 

3 Atualizar prod_os.fk_serv com o Primeiro Serviço Encontrado

Este método atualiza a coluna fk_serv com o id do primeiro serviço encontrado para cada fk_os.

 

primeiro_servico_encontrado.php

 

<?php
require_once('Database.php');

$db = new Database();

// Atualiza fk_serv com o id do primeiro serviço encontrado para cada fk_os
$sql = "
    UPDATE prod_os
    JOIN (
        SELECT fk_os, MIN(id) AS min_serv_id
        FROM serv_os
        GROUP BY fk_os
    ) AS first_serv ON prod_os.fk_os = first_serv.fk_os
    SET prod_os.fk_serv = first_serv.min_serv_id;
";

try {
    $db->query($sql);
    echo "Atualização com o primeiro serviço encontrado realizada com sucesso.<br>";
} catch (Exception $e) {
    echo "Erro na atualização com o primeiro serviço encontrado: " . $e->getMessage() . "<br>";
}
?>

 

4 Atualizar prod_os.fk_serv com Base em um Critério Específico

Este método atualiza prod_os.fk_serv com base em um critério específico, como a descrição do serviço.

base_em_um_criterio_especifico.php

<?php
require_once('Database.php');

$db = new Database();

// Atualiza fk_serv com base em um critério específico para cada fk_os
$sql = "
    UPDATE prod_os
    JOIN (
        SELECT s.fk_os, s.id AS serv_id
        FROM serv_os s
        JOIN (
            SELECT fk_os, MIN(id) AS min_serv_id
            FROM serv_os
            WHERE descricao = :descricao
            GROUP BY fk_os
        ) AS x ON s.fk_os = x.fk_os AND s.id = x.min_serv_id
    ) AS specific_serv ON prod_os.fk_os = specific_serv.fk_os
    SET prod_os.fk_serv = specific_serv.serv_id;
";

$params = ['descricao' => 'Serviço X']; // Defina o critério de serviço

try {
    $db->query($sql, $params);
    echo "Atualização com base em critério específico realizada com sucesso.<br>";
} catch (Exception $e) {
    echo "Erro na atualização com base em critério específico: " . $e->getMessage() . "<br>";
}
?>

 

5 Verificar o Resultado da Atualização

Este método verifica se a atualização foi realizada corretamente e imprime a contagem de serviços para cada fk_os.

resultado_de_atualizacao.php

<?php
require_once('Database.php');

$db = new Database();

// Verifica o resultado da atualização
$sql_check = "
    SELECT p.fk_os, COUNT(*) as count_serv
    FROM prod_os p
    JOIN serv_os s ON p.fk_os = s.fk_os
    GROUP BY p.fk_os;
";

try {
    $results = $db->query($sql_check, [], true);
    if (count($results) > 0) {
        foreach ($results as $row) {
            echo "fk_os: " . $row["fk_os"] . " - Count of Services: " . $row["count_serv"] . "<br>";
        }
    } else {
        echo "0 resultados";
    }
} catch (Exception $e) {
    echo "Erro ao verificar o resultado da atualização: " . $e->getMessage() . "<br>";
}
?>

 

6 Fechar a Conexão com o Banco de Dados

O PDO fecha automaticamente a conexão quando o script termina. No entanto, se você quiser explicitamente encerrar a conexão, pode definir $db->conn = null;.

 

<?php
$db->conn = null; // Fecha a conexão com o banco de dados
?>

Caso achar melhor aqui está o script PHP completo combinando todas as operações com a classe Database:

 

<?php
class Database {
    private $host = 'localhost';
    private $db_name = 'bancodedados';
    private $username = 'usuario';
    private $password = 'senha';
    public $conn;

    public function __construct() {
        $this->connect();
    }

    private function connect() {
        $dsn = "mysql:host={$this->host};dbname={$this->db_name};charset=utf8";
        try {
            $this->conn = new PDO($dsn, $this->username, $this->password);
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            echo "Connected successfully<br>";
        } catch (PDOException $e) {
            echo "Connection failed: " . $e->getMessage();
            exit;
        }
    }

    public function query($sql, $params = [], $fetch = false) {
        $stmt = $this->conn->prepare($sql);
        $stmt->execute($params);
        if ($fetch) {
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        }
        return $stmt;
    }
}

// Conectar ao banco de dados
$db = new Database();

// Atualiza fk_serv com o id do serviço mais recente para cada fk_os
$sql = "
    UPDATE prod_os
    JOIN (
        SELECT fk_os, MAX(id) AS max_serv_id
        FROM serv_os
        GROUP BY fk_os
    ) AS latest_serv ON prod_os.fk_os = latest_serv.fk_os
    SET prod_os.fk_serv = latest_serv.max_serv_id;
";

try {
    $db->query($sql);
    echo "Atualização com o serviço mais recente realizada com sucesso.<br>";
} catch (Exception $e) {
    echo "Erro na atualização com o serviço mais recente: " . $e->getMessage() . "<br>";
}

// Atualiza fk_serv com o id do primeiro serviço encontrado para cada fk_os
$sql = "
    UPDATE prod_os
    JOIN (
        SELECT fk_os, MIN(id) AS min_serv_id
        FROM serv_os
        GROUP BY fk_os
    ) AS first_serv ON prod_os.fk_os = first_serv.fk_os
    SET prod_os.fk_serv = first_serv.min_serv_id;
";

try {
    $db->query($sql);
    echo "Atualização com o primeiro serviço encontrado realizada com sucesso.<br>";
} catch (Exception $e) {
    echo "Erro na atualização com o primeiro serviço encontrado: " . $e->getMessage() . "<br>";
}

// Atualiza fk_serv com base em um critério específico para cada fk_os
$sql = "
    UPDATE prod_os
    JOIN (
        SELECT s.fk_os, s.id AS serv_id
        FROM serv_os s
        JOIN (
            SELECT fk_os, MIN(id) AS min_serv_id
            FROM serv_os
            WHERE descricao = :descricao
            GROUP BY fk_os
        ) AS x ON s.fk_os = x.fk_os AND s.id = x.min_serv_id
    ) AS specific_serv ON prod_os.fk_os = specific_serv.fk_os
    SET prod_os.fk_serv = specific_serv.serv_id;
";

$params = ['descricao' => 'Serviço X']; // Defina o critério de serviço

try {
    $db->query($sql, $params);
    echo "Atualização com base em critério específico realizada com sucesso.<br>";
} catch (Exception $e) {
    echo "Erro na atualização com base em critério específico: " . $e->getMessage() . "<br>";
}

// Verifica o resultado da atualização
$sql_check = "
    SELECT p.fk_os, COUNT(*) as count_serv
    FROM prod_os p
    JOIN serv_os s ON p.fk_os = s.fk_os
    GROUP BY p.fk_os;
";

try {
    $results = $db->query($sql_check, [], true);
    if (count($results) > 0) {
        foreach ($results as $row) {
            echo "fk_os: " . $row["fk_os"] . " - Count of Services: " . $row["count_serv"] . "<br>";
        }
    } else {
        echo "0 resultados";
    }
} catch (Exception $e) {
    echo "Erro ao verificar o resultado da atualização: " . $e->getMessage() . "<br>";
}

// Fechar a conexão com o banco de dados
$db->conn = null;
?>

 

[clovis.sardinha 1]

@Jack Oliveira muito obrigado pela completíssima orientação. Vou implementá-la.  Abraços.