Assigning SELECT to UPDATE command after concatenating in MySql

Question

I want to fill the password in user table by concatenating 4 char from user name and last 5 digits from the accountnumber in Employee table(another table)

I tried the below query,but it returns empty.

SET SQL_SAFE_UPDATES=0;
UPDATE user set password = (SELECT (CONCAT(SUBSTRING(user.username,1,4), SUBSTRING(employee.accountnumber,-5))) FROM employee WHERE user.employeename= employee.employeename);
SET SQL_SAFE_UPDATES=1;

When i tried SELECT statement seperately in editor,it returns exact values.

SELECT (CONCAT(SUBSTRING(user.username,1,4), SUBSTRING(employee.accountnumber,-5))) as pwd
FROM employee ,user WHERE user.employeename= employee.employeename;

xxxx03332
yyyy07674

But in UPDATE statement it is not working,as im setting password column as 'NOT NULL' option.when i execute in editor it shows error as Error Code: 1048. Column 'password' cannot be null I tried below query,but same error

UPDATE user password , (SELECT (CONCAT(SUBSTRING(user.username,1,4), SUBSTRING(employee.accountnumber,-5))) FROM employee WHERE user.employeename= employee.employeename);

If i include 'user'(table name) in FROM clause,it shows error as Error Code: 1093. You can't specify target table 'user' for update in FROM clause So, i deleted that in UPDATE Statement.

fancyPants · Accepted Answer · 2015-10-29 11:25:52Z

1

Why so complicated? Do it like this:

UPDATE user u
JOIN   employee e ON u.employeename = e.employeename
SET    u.password = Concat(Substring(u.username, 1, 4),
                          Substring(e.accountnumber, -5)) 
;

answered Oct 29, 2015 at 11:25

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Assigning SELECT to UPDATE command after concatenating in MySql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related