TomPHP TomPHP - 1 year ago 63
MySQL Question

Update Select with where & CONCAT issue

I am trying to update a table with another table data after concating two columns It shows errors.


Error Code: 1064. You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax


Table 1: orders
Table 2: users

I am trying to update orders table field
name
by comparing a field with
user_id
in orders table.

The
user_id
is linked to users table. I am trying to concat
first_name
&
last_name
and update the
name
in orders table.

UPDATE orders SET
(name =
SELECT CONCAT(u.first_name, ' ', u.last_name) as full_name
FROM users u
WHERE u.first_name IS NOT NULL OR u.first_name <> '' );


The condition goes like this.

If any id present in
user_id
in orders table it should check
users
table
id
and select & concat
first_name
&
last_name
and update the order table.

If no
user_id
in orders table the field should be updated with
NONE
.

Its complicate for me to do it via Query. I can do that in PHP but its not recomended. Can some one help.

83N 83N
Answer Source

Assuming you have a user_id column on the orders table to join to, you could try this:

UPDATE orders o SET o.name = 
   IFNULL((SELECT CONCAT(u.first_name, ' ', u.last_name) AS full_name 
           FROM users u 
           WHERE u.user_id = o.user_id 
           AND u.first_name IS NOT NULL 
           AND u.first_name <> ''), 'NONE');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download