2

I was studying this question:
MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query
and this one:
MySQL Insert & Joins

And my question is an amalgamation of the two.

Essentially what I'm trying to do is UPDATE multiple rows with different values and I want to JOIN another table to check that the current logged user is allowed to UPDATE those rows.

If I understand correctly I can't do this with:

UPDATE beautiful 
JOIN employees on employees.id = beautiful.employee  
SET name='$name', age='$age'
WHERE employees.company = '$company'

because I can't plug in different values for each row.

I investigated using INSERT ON DUPLICATE KEY UPDATE, bearing in mind new rows will not be inserted, only existing rows will be updated.

INSERT INTO beautiful (employee,name, age)
      VALUES
      (1,'Helen', 24),
      (2,'Katrina', 21),
      (3,'Samia', 22),
      (4,'Hui Ling', 25),
      (5,'Yumie', 29) 
ON DUPLICATE KEY UPDATE
       name= VALUES(name), age = VALUES(age)

The problem with this, again if I understand correctly, with INSERT I can only JOIN tables if I INSERT from a SELECT statement

but if I use a SELECT statement eg:

INSERT INTO beautiful (employee,name, age)
SELECT '1','Helen', '24'
FROM beautiful
JOIN employees on employees.id = beautiful.employee and employee.company = '$company'
ON DUPLICATE KEY UPDATE
name= VALUES(name), age = VALUES(age)

I'm back to the problem of only being able to UPDATE a single row because I can't use more than one set of literal values in the SELECT statement.

How may I go about updating multiple rows with different values and joining each row on another table?

Community
  • 1
  • 1
andrew
  • 9,313
  • 7
  • 30
  • 61

1 Answers1

1

You should use Union all:

INSERT INTO beautiful (employee,name, age)
SELECT * from 
FROM (
select '1','Helen', '24' 
union all
select '2','Katrina', '21' 
) beautiful
JOIN employees on employees.id = beautiful.employee and employee.company = '$company'
ON DUPLICATE KEY UPDATE
name= VALUES(name), age = VALUES(age)

and you also can use PHP to make that union all syntax - eg: implode(' union all', $array_of_selects)

miraged
  • 26
  • 1
  • Thats brilliant, I cant remember how i solved the problem two years ago, but I'll bear this in mind going forward! – andrew Oct 04 '17 at 18:38