SQL Field Update Syntax Issues with complex selection logic

Question

The query below works fine and is what I need. I want to add a new keyword onto "Keywords" e.g.

UPDATE bugs SET bug.keywords = CONCAT(bug.keywords, ', Report:DevProcess')

However no matter where I place this in the logic below, I get a syntax error.

The web examples I have seen and in Stackoverflow are for simple Update ... WHERE .... examples.

SET @StartDate = '2016-03-01';
SET @EndDate = '2016-03-31';

SELECT
    bugs_activity.bug_id,
    bug.status_whiteboard AS Whiteboard,
    bug.keywords AS Keywords,
    bug.bug_status,
    bug.resolution,
    SUM(CASE WHEN fd.name = 'bug_status' AND (bugs_activity.added = 'VERIFIED' OR bugs_activity.added = 'CLOSED') THEN 1 ELSE 0 END) AS ClosedCount,
    MIN(CASE WHEN fd.name = 'bug_status' AND bugs_activity.added = 'VERIFIED' THEN bug_when ELSE NULL END) AS verifiedDate,
    MIN(CASE WHEN fd.name = 'bug_status' AND bugs_activity.added = 'CLOSED' THEN bug_when ELSE NULL END) AS closedDate
FROM bugs_activity
INNER JOIN bugs bug
    ON bugs_activity.bug_id = bug.bug_id
INNER JOIN fielddefs fd
    ON bugs_activity.fieldid = fd.id
WHERE
    (bugs_activity.bug_when BETWEEN '2015-09-01' AND @EndDate)
    AND (Keywords LIKE '%Region:Europe%')
    AND NOT (Keywords LIKE '%Report:DevProcess%')
GROUP BY bug_id
HAVING 
    ClosedCount > 0
    AND (
        (verifiedDate IS NOT NULL AND verifiedDate >= @StartDate)
        OR (verifiedDate IS NULL AND (closedDate IS NOT NULL AND closedDate >= @StartDate))
    )

Additional info from questions: Linqpad SQL talking to MySQL DB

From linqpad - 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 'UPDATE bugs SET bug.keywords = CONCAT(bug.keywords, ', Report:DevProcess')' at line 20

I removed GroupBy line, same error. CONCAT is what a web search tells me How to prepend a string to a column value in MySQL?

In the selected "bugs" I want to "UPDATE bugs SET bug.keywords = CONCAT(bug.keywords, ', Report:DevProcess') "

Bugzilla has historically for a long time used multiple keywords. It is what it is whether good or bad.

== 31/05/2016 update ==

I simplified the query and got past the syntax error, however no update. I confirmed account had DB write access by using the read account which produced an access denied error.

-- this shows the one record 
SELECT bug_id
FROM bugs
WHERE (bugs.bug_status = 'VERIFIED')  AND (bugs.status_whiteboard LIKE '%Leiden%') and (bugs.keywords LIKE '%Region:Europe%') AND NOT (bugs.keywords LIKE '%Report:DevProcess%') 

-- this runs without an error but shows no records updated
UPDATE bugs SET bugs.keywords = CONCAT(bugs.keywords, ', Report:DevProcess') 
WHERE (bugs.bug_status = 'VERIFIED')  AND (bugs.status_whiteboard LIKE '%Leiden%') and (bugs.keywords LIKE '%Region:Europe%') AND NOT (bugs.keywords LIKE '%Report:DevProcess%')

You have `GROUP BY`. You can not have update statement with `GROUP BY`. — Giorgi Nakeuri, May 30 '16 at 06:41
can you post your complete update statement. the update query you provide seems static. which of the columns in the select statement you used for update? — bmsqldev, May 30 '16 at 06:55
Why did you tag this with `sql-server` if you are clearly using `mysql`? But you shouldn't be storing comma separated values in a single column anyway. — , May 30 '16 at 07:41

score 0 · Answer 1 · answered May 31 '16 at 03:06

The simpler syntax does "work", i.e. no syntax error however the write did not work initially. This turned out to be because the keyword schema required the keyword to be pre-defined. Adding the keyword in resulting in the record being updated.

-- this runs without an error but shows no records updated
UPDATE bugs SET bugs.keywords = CONCAT(bugs.keywords, ', Report:DevProcess') 
WHERE (bugs.bug_status = 'VERIFIED')  AND (bugs.status_whiteboard LIKE '%Leiden%') and (bugs.keywords LIKE '%Region:Europe%') AND NOT (bugs.keywords LIKE '%Report:DevProcess%')

SQL Field Update Syntax Issues with complex selection logic

1 Answers1