MySql combine records from same table

36
April 16, 2019, at 03:40 AM

I'm attempting to preform a sql command to update rows, and combine rows with matching data.

As an example of what I'm attempting, I have a table as shown below. I want to update all records that have a userEmail = user@user.com to become admin@example.com. If I attempt a simple update I encounter duplicate key errors.

I could work around this by adding some kind of counter as a surrogate key, but I'd rather avoid that option if possible.

Also, as a secondary feature if possible, I'd like to combine the quantity of the records.

What kind of operation can I use for this?

Original table
___________________________________________________________________________
userEmail             |productId   |usItemId |quantity|timestamp          |
----------------------|------------|---------|--------|-------------------|
admin@example.com     |111111111111|222222222|      77|2019-04-07 02:19:41|<--
admin@example.com     |xxxxxxxxxxxx|xxxxxxxxx|       1|2019-04-07 02:19:41|
admin@example.com     |xxxxxxxxxxxx|xxxxxxxxx|       1|2019-04-07 02:19:41|
user@user.com         |111111111111|222222222|      22|2019-04-07 02:19:41|<--
user@user.com         |xxxxxxxxxxxx|xxxxxxxxx|       1|2019-04-06 05:34:58|
user@user.com         |xxxxxxxxxxxx|xxxxxxxxx|       1|2019-04-06 05:35:30|

Updated table
___________________________________________________________________________
userEmail             |productId   |usItemId |quantity|timestamp          |
----------------------|------------|---------|--------|-------------------|
admin@example.com     |111111111111|222222222|      99|2019-04-07 02:19:41|<--
admin@example.com     |xxxxxxxxxxxx|xxxxxxxxx|       1|2019-04-07 02:19:41|
admin@example.com     |xxxxxxxxxxxx|xxxxxxxxx|       1|2019-04-07 02:19:41|
admin@example.com     |xxxxxxxxxxxx|xxxxxxxxx|       1|2019-04-06 05:34:58|
admin@example.com     |xxxxxxxxxxxx|xxxxxxxxx|       1|2019-04-06 05:35:30|
Answer 1

I would suggest two steps:

insert into original (userEmail, productId, usItemId, quantity, timestamp)
    select 'admin@example.com', productId, usItemId, quantity, timestamp
    from original o
    where o.userEmail = 'user@user.com'
    on duplicate key update quantity = values(quantity) + quantity;
delete from original
    where o.userEmail = 'user@user.com';
READ ALSO
How to query between date and specific time range

How to query between date and specific time range

I need help on a query between date and specific time rangeFor example, a user entering the building from 1 Jan 2019 until 1 April 2019 and only from 7 PM until 10 PM for that date

39
How to invert or filter out the two employees that work on projects in other departments?

How to invert or filter out the two employees that work on projects in other departments?

So right now I am so close but right now the result table that is return only shows the employees that I don't want to see and I want to show the employees that only work on projects within their department

26
Is there a way to hide all database info such as password, username, etc?

Is there a way to hide all database info such as password, username, etc?

I'm creating several php scriptsMust I always insert the host, username, password, etc

31
SQL filter out ids that have multiple values

SQL filter out ids that have multiple values

I have MySQL table in the following format

32