1

I added a column to my WorkSheetTransaction table and want to populate it with a name built from the Department table. Both tables are already populated with the join field DepartmentId.

The following query runs ok but no rows are updated. Why not?

update WorkSheetTransactions
inner join Departments on WorkSheetTransactions.DepartmentId = Departments.DepartmentId 
set WorkSheetTransactions.DepartmentName = (Departments.GL_Account + '-' + Departments.DepartmentName)

I've tried many variations but I just can't see where I've gone wrong. BTW, the join field is an integer in both tables and all the other 2 fields are var_chars.

Dave Hackett
  • 51
  • 1
  • 4
  • Generally, it's best to handle issues of data display in application level code. Keep your data nice and normalised. – Strawberry Jan 09 '17 at 02:16

1 Answers1

0

In mysql, you should use concat to concatenate strings:

UPDATE WorkSheetTransactions
INNER JOIN Departments
ON WorkSheetTransactions.DepartmentId = Departments.DepartmentId 
SET WorkSheetTransactions.DepartmentName = concat(Departments.GL_Account, '-', Departments.DepartmentName)
Blank
  • 12,308
  • 1
  • 14
  • 32