Mysql field update and its subsequent use

Question

I have a purely theoretical question, with a meaningless example:

UPDATE mytable binaryData = '___GIANT_BINARY_DATA___', isBig = LENGTH('___THE_SAME_GIANT_BINARY_DATA___') > 1000000000 WHERE id = 22 

Now, if my binary data is “million bytes”, I want to avoid writing twice in plain SQL

  UPDATE mytable binaryData = '___GIANT_BINARY_DATA___', isBig = LENGTH(binaryData) > 1000000000 WHERE id = 22 

I want to update a column field and then reuse it using its column name in the same query

or maybe there is a way to define an alias in the UPDATE syntax, how can I do with SELECT?

early

(ps I'm also interested in the equivalent INSERT syntax)

Answer 1

MySql is the oddity that SET statements are non-atomic, which means that as soon as a new value is assigned to one column, this new value will be reflected if it is used elsewhere in the update statement.

The following statements:

 CREATE TABLE Swap ( a CHAR(1), b CHAR(1) ); INSERT INTO Swap (a, b) VALUES ('a', 'b'); UPDATE Swap SET a = b, b = a; SELECT * FROM Swap; 

The result will be b , b in MySql, but b , a in every other RBDMS I know about ...

So, for your question, you do not need the binaryData alias, because once it is updated, the updated value will be reflected in the isBig assignment isBig .

However, it is probably a bad idea to rely on this behavior because it is non-standard.

Answer 2

You can use CROSS JOIN like this:

 UPDATE mytable a CROSS JOIN (SELECT '__GIANT_BINARY_DATA__' AS bindata) b SET a.binaryDate = b.bindata, a.isBig = LENGTH(b.bindata) > 1000000000 WHERE a.id = 22 

Which will give you access to the same value in each row, and you will only need to pass the data in the SQL statement line only once.

Answer 3

You can use a custom variable:

 set @content = '___GIANT_BINARY_DATA___'; UPDATE mytable SET binaryData = @content, isBig = LENGTH(@content) > 1000000000 WHERE id = 22; set @content = NULL; -- free up memory