1

With MYSQL I can insert into a table using a select statement with where clause

INSERT INTO Targeted (field1,field2) SELECT field1,field2 FROM Table WHERE id=$id

And now i trying to do update with select too...Here is how my table look like

 tagged                tags
---------          -------------
fid   tid            id  amount
 1     2              1    0
 1     1              2    0
 2     2              3    0
 3     3              4    0
 3     4

How do i select fid from table tagged with specific WHERE clause like fid = 1 Then update table tags with returned result from tagged

Input
fid = 1

Desired output
update tags with 1 amount=amount+1
update tags with 2 amount=amount+1

I been through some research on SO question here is how my query look like but its not giving any error or changes too

UPDATE
     tags
SET
     tags.amount = tags.amount+1
FROM
     tags
INNER JOIN     
     tagged
ON     
     tags.id = tagged.tid 
WHERE
     tagged.fid  = 1
Community
  • 1
  • 1
Leon Armstrong
  • 1,285
  • 3
  • 16
  • 41

1 Answers1

1

You got your UPDATE syntax wrong. In MySql table refs go first and then SET clause.

Try

UPDATE tags t JOIN tagged d
    ON t.id = d.tid 
   SET t.amount = t.amount + 1
WHERE d.fid  = 1

After an update tags table will look like

| ID | AMOUNT |
---------------
|  1 |      1 |
|  2 |      1 |
|  3 |      0 |
|  4 |      0 |

Here is SQLFiddle demo

peterm
  • 91,357
  • 15
  • 148
  • 157