CODEWITHSUNDEEP
mysqlsql
codepuppy
codepuppy

Reputation: 1140

What does := denote within the context of a placeholder

This is probably a dumb question to those that know, but here goes.

Experimenting with using a placeholder in a query. I find that the following works:

SELECT day, @dy:=CAST( CASE day
                         WHEN 'Su' THEN 1
                         WHEN 'Mo' THEN 2
                         WHEN 'Tu' THEN 3
                         WHEN 'Wed' THEN 4
                         WHEN 'Th' THEN 5
                         WHEN 'Fr' THEN 6
                         WHEN 'Sa' THEN 7
                     END AS UNSIGNED) as dayofweek 
 FROM `Schedule_Repetition` WHERE repetition_Key='426';

But this does not work 

SELECT day, @dy=CAST( CASE day
                         WHEN 'Su' THEN 1
                         WHEN 'Mo' THEN 2
                         WHEN 'Tu' THEN 3
                         WHEN 'Wed' THEN 4
                         WHEN 'Th' THEN 5
                         WHEN 'Fr' THEN 6
                         WHEN 'Sa' THEN 7
                     END AS UNSIGNED) as dayofweek 
 FROM `Schedule_Repetition` WHERE repetition_Key='426';

I see all sorts of examples of placeholders some of which use @P=..... and others which use @P:=....... Please could someone explain what the := denotes and why the syntax varies and or point the way to the documentation which explains to use of :=.

Upvotes: 2

Views: 67

Answers (1)

James Hill
James Hill

Reputation: 61812

:= is an assignment operator. The information below is from the documentation on dev.mysql.com.

Assignment operator. Causes the user variable on the left hand side of the operator to take on the value to its right. The value on the right hand side may be a literal value, another variable storing a value, or any legal expression that yields a scalar value, including the result of a query (provided that this value is a scalar value).

The difference between = and :=:

= Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement) := Assign a value

Unlike =, the := operator is never interpreted as a comparison operator. This means you can use := in any valid SQL statement (not just in SET statements) to assign a value to a variable.

Check out the full list of operators on dev.mysql.com.

Upvotes: 4

Related Questions