INSERT ... ON DUPLICATE KEY UPDATE - like so?

Databases
1

Hello all,

I do have this case where I need to store some domain names and the type of operation and operation date, to later be displayed.

I do have a “natural” primary key (if we may say so?) on this case, that is the domain name.

The domain names will be a varchar. No issues.
Operation date will be catch by timestamp. No issues here as well. (I suppose).

So, instead of doing two methods, one for insert and another for update, I was thinking if I can do just one. If no record is present, insert, if not, update.

I end up on INSERT ON DUPLICATE KEY UPDATE.

Question:


INSERT INTO a.atualizaDominio
  (a.nomeDominio,a.tipoAtualizacao) 
   VALUES (?,?) ON DUPLICATE KEY UPDATE 
a.tipoAtualizacao=VALUES(a.tipoAtualizacao);

note: The ? are placeholders from PDO that’s why I have them there.

I’m getting this error:
INSERT,UPDATE command denied to user…

but I do have the privileges, so I believe it should be something to do with the query sintax?

Update: if I remove the alias I can run the command. But I’ve seen many of you using alias… and I believe it’s a best practice… ? :smiley:

Would you not use an autoincrement ID column in this case, since the domain names serves as natural keys?

Thanks in advance,
Márcio

2

Sorry for this late reply. Thanks you all. As always, 5 stars! :slight_smile:

3

You’re setting the value of a field to the value it already has. What purpose does that serve? Are you maybe looking for INSERT IGNORE?

4

Ah, so that is what is VALUES() does there, I thought that was a typo :blush:
Another thing learned. Thanks Rudy :slight_smile:

5

if we remove the erroneous aliases, that’s actually the way it’s supposed to be

we’re setting the value of the column to be equal to the value it would’ve got in the INSERT

this is all nicely explained in da manual

:slight_smile:

6

yes, it’s a good practice when (a) it helps understanding, and (b) the syntax allows it

in this case the aliases fail condition (b) :slight_smile:

that is correct, i would not

:slight_smile: