CODEWITHSUNDEEP
mysqlrows
Surendra Kumar B
Surendra Kumar B

Reputation: 118

Update multiple rows with the value from the selected column

I am new to SQL, I have a requirement where I have to increment the value in a specific column by 1 and update that value in the same column based on the id.

Here is what I want,

    UPDATE books SET version_num = 
  (SELECT (version_num + 1) FROM books WHERE book_recid IN 
('72b72282-707b-4dd4-ab08-f5a085e92a2b', '73255df2-413e-4aad-892d-edc08ffa3405'))
WHERE book_recid IN 
('72b72282-707b-4dd4-ab08-f5a085e92a2b', '73255df2-413e-4aad-892d-edc08ffa3405');

I know the above query won't work. I want a query to update the values in the database like above.

Upvotes: 0

Views: 64

Answers (3)

RiggsFolly
RiggsFolly

Reputation: 94662

It is simple to add 1 to a column.

You have just over complicated your query.

UPDATE books SET version_num = version_num +1
WHERE book_recid IN ('72b72282-707b-4dd4-ab08-f5a085e92a2b', 
                     '73255df2-413e-4aad-892d-edc08ffa3405');

Upvotes: 1

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

Do like this

UPDATE books
    SET version_num = tb1.vers
from (
    SELECT (version_num + 1) as vers FROM tag_config_params
     WHERE book_recid IN
  ('72b72282-707b-4dd4-ab08-f5a085e92a2b', '73255df2-  413e-4aad-892d-edc08ffa3405')
) tb1
WHERE book_recid IN 
('72b72282-707b-4dd4-ab08-f5a085e92a2b', '73255df2-413e-4aad-892d-edc08ffa3405');

Upvotes: 0

Neil Twist
Neil Twist

Reputation: 1159

You can do a from in the update query

UPDATE A SET col1 = something
FROM B
INNER JOIN C on B.col4 = C.col5
WHERE A.col2 = B.col3

In your case, it could be something like the following

UPDATE books b SET version_num = tcp.version_num + 1
FROM tag_config_params tcp
WHERE tcp.book_recid = b.book_recid
AND tcp.book_recid IN ('72b72282-707b-4dd4-ab08-f5a085e92a2b', '73255df2-413e-4aad-892d-edc08ffa3405');

I'm assuming that book_recid is a foreign key relationship here.

Upvotes: 0

Related Questions