INSERT INTO NOT EXISTS

Asked

Viewed 3,959 times

2

I need to create an INSERT where it is checked if the value already exists. I looked at some posts, but I am not able to execute.

INSERT INTO `pagina_extra` (`ID_PExtra`, `ordem_paginas`, `id_menu`, `url_seo`, `url`, `nome`, `titulo`, `conteudo`, `exibir`, `interno`)
SELECT * FROM (SELECT 'NULL', '3', '1', 'downloads', 'pag_downloads.php', 'Downloads', '', '', 'N', 'N') AS tmp
WHERE NOT EXISTS (
    SELECT url_seo FROM pagina_extra WHERE url_seo = 'downloads'
) LIMIT 1;

Error that appears when I test directly in Phpmyadmin:

Duplicate column name 'Downloads'

But there is no value to double accuse. What is wrong?

php mysql phpmyadmin
  • 1

    query is not seeing words with case sensitive then when Voce says 'downloads' and 'Downloads' is the same thing.

  • And what is the solution?

  • I do not know at what point of the development you this but the ideal was to leave the names of the columns different, even because it gets very strange and maybe it generates some future disorder having 2 columns with the same name.

  • solved your problem ?

3 answers

1

You need to give an alias for each column of your select, because the error says that the columns have equal names.

The result will be like this:

INSERT INTO `pagina_extra` (`ID_PExtra`, `ordem_paginas`, `id_menu`, `url_seo`, `url`, `nome`, `titulo`, `conteudo`, `exibir`, `interno`)
SELECT * FROM (SELECT 'NULL' AS ID_PExtra,
                '3' AS ordem_paginas,
                '1' AS id_menu,
                'downloads' AS url_seo,
                'pag_downloads.php' AS url,
                'Downloads' AS nome,
                '' AS titulo,
                '' AS conteudo,
                'N' AS exibir,
                'N' AS interno) AS tmp
WHERE NOT EXISTS (
    SELECT url_seo FROM pagina_extra WHERE url_seo = 'downloads'
) LIMIT 1;

0

Test as follows:

DECLARE @Var VARCHAR(50)
SET @Var = (SELECT url_seo FROM pagina_extra WHERE url_seo = 'downloads')
IF @Var <> 'downloads'
BEGIN
    INSERT INTO pagina_extra (ID_PExtra, ordem_paginas, id_menu, url_seo, url, nome, titulo, conteudo, exibir, interno)
    SELECT * FROM (SELECT 'NULL', '3', '1', 'downloads', 'pag_downloads.php', 'Downloads', '', '', 'N', 'N') AS tmp
END

0

You can use the LOWER to make the value of its field identical. 

INSERT INTO `pagina_extra` (`ID_PExtra`, `ordem_paginas`, `id_menu`, `url_seo`, `url`, `nome`, `titulo`, `conteudo`, `exibir`, `interno`)
SELECT * FROM (SELECT 'NULL', '3', '1', 'downloads', 'pag_downloads.php', 'Downloads', '', '', 'N', 'N') AS tmp
WHERE NOT EXISTS (
    SELECT url_seo FROM pagina_extra WHERE LOWER(url_seo) = LOWER('downloads')
) LIMIT 1;

Browser other questions tagged php mysql phpmyadmin

You are not signed in. Login or sign up in order to post.