SQL Update help

Question

I have a really simple question, is it possible to update a table with new values using just one update statement.

Say for example I have a table with author, title, date, popularity. Now I got some new data which has author name, title corresponding new popularity. How do I update the table now in one statement. Note that author and title are not unique.

Answer 1

If I'm understanding you correctly, something like this should work:

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

...where ? is a placeholder.

Edit: According to your comment, you want to create it if it doesn't exist and update it if it does. I don't think you can do that with one statement, but I might be wrong. In two statements, you could use this to check if it exists already:

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

That would output 1 if it exists or 0 if it doesn't. From there you can choose whether to insert or update.

Answer 2

Since I posted a comment, I'll post an answer as well. @icktoofay posted one solution, but using this link we can follow that it's actually going to cause a table scan, so instead try Jeremiah Clark's solution of:

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

Note that this tries to update first, and if it succeeds, @@ROWCOUNT will be 1 or more, but I'm making the assumption you'll be using T-SQL (Microsoft's SQL language). I don't know that @@ROWCOUNT is supported by any other platforms.

Answer 3

I've used this in SQL Anywhere (not sure what DBMS you're using, though)

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

So if the record exists, it simply updates it. Otherwise, create a new record.

Answer 4

Say my table is at this state

"Briggs" "My Next Master" Jun,2009 10
"Millis" "Man up, Nut head" Mar,2005 3 
"Browne" "Infinite Improbable" Aug,2007 6

And these popularities changed and we have another file with

"Briggs" "My Next Master" 6 
"Millis" "Man up, Nut head" 3

Can we update the corresponding entries in the original table or am I asking something that is not do-able in one statement (using Oracle in Rails).

Answer 5

You can do it in a single statement using Oracle's 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's MERGE statement

Answer 6

Hi Cyborgo

In mySQL you have a command called something called 'ON DUPLICATE KEY UPDATE' and I found the same for oracle (its called Merge)

take a look at this article

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

cheers, sameera