CODEWITHSUNDEEP
mysqlsqlstored-procedures
Ethan Allen
Ethan Allen

Reputation: 14835

How do I take data from one table, and overwrite it from another table, with a SELECT statement in MySQL?

Ok, let me try to explain this question...

I have two tables: movieitems and movieitems_custom. They both have identical columns of barcode, title, runtime, and username.

I want a query or stored procure that will pull from movieitems (to get a movie's details owned by a user) and then pull from and overwrite the return data from movieitems_custom (to get a user's custom data entry).

Note that movieitems_custom doesn't always have a matching entry from movieitems, only sometimes.

Here is an example:

movieitems for a user contains these two items for user 'joe' that he owns:

1234 - Batman Begins - 120 minutes

1235 - 12 Monkeys - 97 minutes

and in movieitems_custom we could have one entry like this for 'joe' that he customized:

1234 - Batman 1 aka Batman Begins - 120 minutes

What I want my statement/procedure to return is this:

1234 - Batman 1 aka Batman Begins - 120 minutes

1235 - 12 Monkeys - 97 minutes

Is some type of JOIN statement enough to make this happen, or am I looking at something more complex?

Upvotes: 0

Views: 61

Answers (2)

AwokeKnowing
AwokeKnowing

Reputation: 8236

you need to use COALESCE which you can read about here

http://msdn.microsoft.com/en-us/library/ms190349.aspx

basically it will let you specify where to get the values from if any are NULL.

Upvotes: 0

planestepper
planestepper

Reputation: 3317

SELECT
    I.barcode,
    IFNULL(C.title, I.title) AS title,
    I.runtime,
    I.username
FROM
    movieitems I 
        LEFT JOIN movieitems_custom C 
        ON I.barcode = C.barcode AND I.username = C.username

Upvotes: 1

Related Questions