my code stock.com

Neil Ludlow

SQL: COALESCE, ELT and JOIN in an INSERT statement
by Neil Ludlow

INSERT 2 values into a new table user_registration_meta, take the data from the users and rep_sessions tables.

ELT picks a random value from $active_reps = 1,2,3,4,5,6.

Because it's a JOIN on an INSERT statement the values in the SELECT parts have to be the same name as the columns you want to insert, here it's user_id and rep_id.

Snippet options

Download: Download snippet as sql-coalesce-elt-and-join-in-an-insert-statement.sql.
Copy snippet: For this you need a free my code stock.com account.
Embed code : You will find the embed code for this snippet at the end of the page, if you want to embed it into a website or a blog!

INSERT INTO user_registration_meta (user_id,rep_id) 
        SELECT users.id AS user_id, 
        COALESCE(rs.rep_id,elt(floor(rand() * $array_length + 1), $active_reps ),0) AS rep_id 
        FROM users 
        LEFT JOIN rep_sessions rs ON users.id=rs.user_id
        WHERE brand_id= $brandid
        GROUP BY users.id
        ORDER BY max(rs.end_date);

Create a free my code stock.com account now.

my code stok.com is a free service, which allows you to save and manage code snippes of any kind and programming language. We provide many advantages for your daily work with code-snippets, also for your teamwork. Give it a try!

Find out more and register now

You can customize the height of iFrame-Codes as needed! You can find more infos in our API Reference for iframe Embeds.