0

So, I have two tables ativos and colaboradores and they are linked by id_colaborador (FK) on my update page, I'm able to change the id_colaborador but instead of changing the ID I want to write the name that match to that ID but I think I need multiple WHERE conditions and SELECT, may anyone help me out? Thanks!
I have the following code to update

$sql = "UPDATE ativos  SET ativo = ?, comentario = ?, data_aquisicao = ?,
                           localizacao = ?, fabricante = ?, modelo = ?,
                           imei = ?, numero_serie = ?, ativo_sap = ?,
                           anexo_a = ?, evento = ?, data_evento = ?,
                         SELECT id_colaborador FROM colaboradores 
                               WHERE nome = ? AND WHERE id_ativo = ?";
  • Please rephrase the question. I'm lost, too. Which row of ativos do you want to change? Is it id_ativos=?? Where should id_colaborado be stored? The text says you want a "name", but the SELECT is fetching an id. Etc. – Rick James Dec 5 '18 at 16:47
1

Your query (while removing PHP code and formatting) looks like

UPDATE ativos  
SET ativo = ?, 
    comentario = ?, 
    data_aquisicao = ?, 
    localizacao = ?, 
    fabricante = ?, 
    modelo = ?, 
    imei = ?,         
    numero_serie = ?, 
    ativo_sap = ?, 
    anexo_a = ?, 
    evento = ?, 
    data_evento = ?, 
    SELECT id_colaborador 
    FROM colaboradores 
    WHERE nome = ? 
    AND WHERE id_ativo = ?

It is syntactically incorrect. Use Multiple-table UPDATE syntax. Final query must look near

UPDATE ativos a
  JOIN colaboradores c 
/* next line maybe wrong, question is not clear */
    ON a.id_ativo = c.id_colaborador 
SET a.ativo = ?, 
    a.comentario = ?, 
    a.data_aquisicao = ?, 
    a.localizacao = ?, 
    a.fabricante = ?, 
    a.modelo = ?, 
    a.imei = ?,         
    a.numero_serie = ?, 
    a.ativo_sap = ?, 
    a.anexo_a = ?, 
    a.evento = ?, 
    a.data_evento = ?
/* next line maybe wrong, question is not clear */
WHERE c.nome = ? 
-1

If I've understood your question correctly, you can join the table and set the column to be the name instead of the ID:

UPDATE a SET a.id = b.Name FROM TableA a INNER JOIN TableB b ON a.ID = b.ID

  • 1
    The question is tagged as MySQL-related. "Syntax Error" guaranteed. – Akina Dec 5 '18 at 13:18

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.