CODEWITHSUNDEEP
mysqlsql
dierre
dierre

Reputation: 7220

locking a table just for my query during an update?

I'm using mysql 5.5.22. Sometimes it happens that I need to take some data from somewhere and this somewhere is a system table.

E.g.:

I have this query (written by my colleague):

UPDATE _x_eia_marcatura AS X, A_EVENTS AS E 
SET X.kind = E.kind, X.ref_id = E.ref_id 
WHERE E.DELIVERY_ID=49 AND E.kind='c' AND E.ref_id = 69 
AND X.MAILQ_ID = E.MAILQ_ID;

Now if you by accident write E.kind = X.kind you're updating the system table. Is there a way to tell mysql "if I'm updating table E then don't do anything" ?

I know that this alias naming convention is crappy but it made me think maybe there is a way to do something I suggested.

Upvotes: 2

Views: 107

Answers (1)

Cylindric
Cylindric

Reputation: 5894

You could use permissions if it's a global "never insert into this table" thing, or perhaps triggers.

Generally speaking though, no. Code is normally considered "trusted" so whatever it asks for is considered acceptable. If you really need this, you should investigate stored procedures and only allow database changes through these.

Upvotes: 3

Related Questions