UPDATE 1 table or another table depending on WHERE statement

Question

I have a UNION statement to retrieve data from 2 tables using a WHERE clause.

Just wondering if it is possible to have a SQL statement to UPDATE a column (Comments) from either table1 or table2 depending on which table that data is within.

I was thinking something along the lines of below...

UPDATE `table1` OR `table2` 
SET `Comments` = '$AddComment' 
WHERE `column` = '$GetColumn'

I have tried the statement within the question, however receive the following error message...

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 'OR table2 SET Comments = 'comment' WHERE column = '$GetColumn' at line 1

***The thing is though, what i am trying to update is only within one of those tables and not both

Welcome to Stack Overflow! This question is a little short on information. Can you share what you have tried, and what problems have you run into? — Jay Blanchard, Feb 09 '15 at 17:54
Hi, i have tried the statement within the question, however receive the following error message "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 'OR `table2` SET `Comments` = 'comment' WHERE `column` = '$GetColumn' at line 1" — Luke Higgins, Feb 09 '15 at 17:59
@JayBlanchard I think that IS what OP's trying as actual code, and is getting an syntax error, because of the `OR`. `or the right syntax to use near 'OR` obviously being incorrect/invalid. — Funk Forty Niner, Feb 09 '15 at 18:00
Without getting into a stored procedure that can start with a condition statement I don't think that this can be done — Jay Blanchard, Feb 09 '15 at 19:00

score 1 · Accepted Answer · answered Feb 09 '15 at 18:07

1

UPDATE `table1` 
SET `Comments` = '$AddComment' 
WHERE `column` = '$GetColumn'

UPDATE `table2` 
SET `Comments` = '$AddComment' 
WHERE `column` = '$GetColumn'

Maybe it is easier do it twice......

answered Feb 09 '15 at 18:07

Deror

51
3

score 0 · Answer 2 · edited May 23 '17 at 10:33

0

You're looking on how to update multiple tables at once, wheres separation should be done via a comma (,), not an OR condition.

Since you wish to check whether a specific condition is set then it makes sense to use CASE, as described in this Stackoverflow answer.

Combining the correct syntax along with the case should result in something like:

UPDATE table1 at, articles bt
SET at.Comments = CASE WHEN at.column = '$GetColumn' AND bt.column <> '$GetColumn' THEN '$AddComment'
    ELSE at.Comments
END
, 
bt.Comments = CASE WHEN bt.column = '$GetColumn' AND at.column <> '$GetColumn' THEN '$AddComment'
    ELSE bt.Comments
END
;

Wherea the set statements are separated via comma and in each case you check whether the correct condition applies or not.

edited May 23 '17 at 10:33

Community

1
1

answered Feb 09 '15 at 18:12

Jonast92

4,964
1
18
32

The thing is though, what i am trying to update is only within one of those tables and not both – Luke Higgins Feb 09 '15 at 18:20
@LukeHiggins I updated my answer, it should look something like this. – Jonast92 Feb 09 '15 at 18:46

score 0 · Answer 3 · answered Feb 09 '15 at 19:26

0

The way I've managed to get around it was to just put 2 UPDATE statements along side of each other instead

UPDATE table1
SET Comments = '$AddComment'
WHERE Column = '$GetColumn'

UPDATE table2
SET Comments = '$AddComment'
WHERE Column = '$GetColumn'

answered Feb 09 '15 at 19:26

Luke Higgins

3
3

This doesn't really "get around" anything; if it does, it's not at all clear what issue or problem you are trying to "get around". Running two statements is just how we do this. If there aren't any rows in the table that match the conditions (predicates in the WHERE clause), then no rows will be updated. If it's important for you to know which query in a `UNION` or `UNION ALL` a row came from, include a discriminator column in the queries. `SELECT 'a' AS src, ... FROM a UNION ALL SELECT 'b' AS src, ... FROM b`. Then you'll be able to tell which query returned a particular row. – spencer7593 Feb 09 '15 at 19:31

UPDATE 1 table or another table depending on WHERE statement

3 Answers3