Search code examples
mysqlsqlmysql-error-1064

Function in SQL update query

I have used function substr in my sql query:

UPDATE staff 
   SET dept_id='".$department."', 
       SUBSTR(auth, 0, 1) = 'l' 
 WHERE staffid='".$name."'

...but query fails:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SUBSTR(auth, 0, 1)='l' WHERE staffid='55000010'' at line 1

I wish to replace first 0th charater upto length 1 of auth column to value 'l'

Solution

  • I looks like you are attempting to replace the first character of the auth field with a l - although it is not entirely clear from your post if that is the intention, or just to prefix it.

    UPDATE staff set dept_id='".$department."', auth=concat('l',SUBSTR(auth, 2))
WHERE staffid='".$name."'