Sql: Update a Row and Returning a Column Value with 1 Query

SQL: Update a row and returning a column value with 1 query

You want the OUTPUT clause

UPDATE Items SET Clicks = Clicks + 1
OUTPUT INSERTED.Name
WHERE Id = @Id

Return updated row attributes on UPDATE

Use the RETURNING clause.

The optional RETURNING clause causes UPDATE to compute and return
value(s) based on each row actually updated. Any expression using the
table's columns, and/or columns of other tables mentioned in FROM, can
be computed. The new (post-update) values of the table's columns are used.

But typically it's smarter to use a join instead of a correlated subquery:

UPDATE t1
SET    foreign_key = t2.id
FROM   t2
WHERE  t2.col = %s
AND    t1.col = %s
RETURNING t1.*;   -- or only selected columns

With your original query, if the subquery finds no row in t2, t1 is updated anyway and t1.col is set to NULL. Typically, you'd rather not update the row in this case, which is what my suggested query does instead.

BTW, target columns in the SET clause cannot be table-qualified (only one table is updated anyway). The manual once more:

Do not include the table's name in the specification of a target
column — for example, UPDATE table_name SET table_name.col = 1 is invalid.

Subquery returns more than 1 row. Using SQL select to update a different tables results

In MySQL, a value of zero appearing in a WHERE clause means false.

So, UPDATE something SET col=val WHERE (SELECT colx FROM sometable) has the potential to be a valid query. If the inner SELECT gets just one row, and its colx column has the value 0, the update won't do anything. If the colx column has a nonzero value the query means UPDATE something SET col=val WHERE true. Accordingly, every row in sometable will be updated. I doubt that's what you want.

If the inner SELECT happens to return more than one row, the query isn't valid. You'll get the error 1242 you actually received.

(This business of interpreting numbers as Boolean values causes MySQL to accept some otherwise dodgy query syntax, like the syntax in your question.)

I guess you want to retrieve the job_queue_id values for the row or rows you actually want to update. So try something like this.

update jobqueue
   set jobqueue.`ignore`= '1'
 where jobqueue.job_queue_id IN (SELECT JobQueue_job_queue_id
                                   FROM mdtinfo
                                  WHERE product_name  = 'Example')

I guessed you have a column jobqueue.job_queue_id. You didn't tell us what columns you have in jobqueue.

Update column value and return updated value in same query MySQL, Java

From the MySQL documentation:

Currently, you cannot update a table and select from the same table in a subquery.

You cannot refer to the table which is being updated in a FROM clause.

You are better off just keeping track of what the new value will be after UPDATE in your Java code.

SQL Server - Is it possible to return an existing column value before a row update?

Try the OUTPUT clause:

declare @previous table(newscore int, Oldscore int, postFK int, userFK int)

UPDATE ForumVotes 
SET score = @newVote 
OUTPUT inserted.score,deleted.score, deleted.postFK, deleted.userFK into @previous
WHERE postFK = @postID
AND userFK = @userID

select * from @previous

Update row value only once when multiple results returned in CASE statement

I would use a CTE to grab the top "error" and join that result to your order table.

;WITH TopError 
AS 
(
    SELECT ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY ExpID ASC) AS ROWID, *
    FROM Excp
)

SELECT * 
-- UPDATE O SET STATUS = CASE WHEN T.EXPID = 'E1' THEN 'Error1' WHEN T.EXPID = 'E2' THEN 'Error2' WHEN T.EXPID = 'E3' THEN 'Error3' ELSE 'EX' END
FROM TopError T
JOIN Order O
  ON T.ORDERID = O.ID
WHERE T.ROWID=1

Try it as a SELECT, if you're satisfied with the results, switch to an UPDATE.

SQL - Update multiple records in one query

Try either multi-table update syntax

UPDATE config t1 JOIN config t2
    ON t1.config_name = 'name1' AND t2.config_name = 'name2'
   SET t1.config_value = 'value',
       t2.config_value = 'value2';

Here is a SQLFiddle demo

or conditional update

UPDATE config
   SET config_value = CASE config_name 
                      WHEN 'name1' THEN 'value' 
                      WHEN 'name2' THEN 'value2' 
                      ELSE config_value
                      END
 WHERE config_name IN('name1', 'name2');

Here is a SQLFiddle demo

mysql update column then select updated value

The best you could imitate is to use two lines of queries, probably using a variable like:

 UPDATE tbl_user SET
     amount = @amount := amount-'$amount'
 WHERE id='$id' LIMIT 1;

 SELECT @amount;

The best you could do then is to create a Stored Procedure like:

 DELIMITER //

 CREATE PROCEDURE `return_amount` ()
 BEGIN
    UPDATE tbl_user SET
     amount = @amount := amount-'$amount'
    WHERE id='$id' LIMIT 1;

    SELECT @amount;
 END //

And then call Stored Procedure in your PHP.

Note: PostgreSQL has this kind of option using RETURNING statement that would look like this:

 UPDATE tbl_user SET amount=amount-'$amount' 
 WHERE id='$id' LIMIT 1
 RETURNING amount

See here

---

Related Topics