SQL Update Help

Question

I have a very simple question: is it possible to update a table with new values ​​using only one update statement.

Say, for example, I have a table with an author, title, date, popularity. Now I have new data that has the name of the author, the name corresponds to the new popularity. How to update the table now in one expression. Please note that the author and title are not unique.

Answer 1

You can do this in one expression with the Oracle MERGE statement:

MERGE DestinationTable target
USING   (
        Select 'Briggs' Author, 'My Next Master' Title, 6 Popularity
        Union All Select 'Millis', 'Man up, Nut head', 3
        ) Z
        ON Z.Author = target.Author
            And Z.Title = target.Title
WHEN MATCHED THEN
    UPDATE SET target.Popularity = Z.Popularity
WHEN NOT MATCHED THEN
    Insert(Author, Title, Popularity) Values(Z.Author, Z.Title, Z.Popularity);

Oracle MERGE statement

Answer 2

, . @icktoofay , , , ,

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
    INSERT INTO Table1 VALUES (...)

, , , @@ROWCOUNT 1 , , T-SQL ( Microsoft SQL). , @@ROWCOUNT .

Answer 3

, - :

update books set popularity=? where author=? and title=?

... ? .

:. , , , , . , , . , , :

select count(*) from books where author=? and title=? limit 1

1, , 0, . , .

Answer 4

SQL Anywhere ( , )

INSERT INTO "books" ("title", "author", "popularity") ON EXISTING UPDATE VALUES(?,?,?);

, , . .

Answer 5

mySQL "ON DUPLICATE KEY UPDATE", ( Merge)

http://blog.mclaughlinsoftware.com/2009/05/25/mysql-merge-gone-awry/

, Sameera