SQL query help, Update Table

[ionbasa]

So, I'm not the best at running SQL queries. I am having trouble updating "table 2" from "table 1"

 

I currently have the following:

UPDATE userspice.users 
SET
	userspice.users.email = mattermost_Aug6Backup.Users.Email,
    userspice.users.username = mattermost_Aug6Backup.Users.Username,
    userspice.users.password = mattermost_Aug6Backup.Users.Password,
    userspice.users.fname = mattermost_Aug6Backup.Users.FirstName,
    userspice.users.lname = mattermost_Aug6Backup.Users.LastName
FROM mattermost_Aug6Backup.Users
	INNER JOIN userspice.users ON userspice.users.username = mattermost_Aug6Backup.Users.Username
WHERE userspice.users.username = mattermost_Aug6Backup.Users.Username

 

I'm not sure what is going wrong? Here's what i'm trying to do: 'mattermost_Aug6Backup' contains the 'Users' table, I'd like to update the 'userspice' 'users' table from it. I'm trying to avoid updating the entire table, but rather update only entries that differ on 'userspice.users'. Usernames on both tables are the same are unique (no double entries).

 

I'm using MariaDB. Thoughts? I get the following error when trying to run the query:

Quote

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM mattermost_Aug6Backup.Users
    INNER JOIN userspice.users ON userspice.users' at line 8

 

[a2martinez]

First post I sent was wrong. You want the OR for not equal

 

UPDATE userspice.users 
SET
	userspice.users.email = mattermost_Aug6Backup.Users.Email,
    userspice.users.username = mattermost_Aug6Backup.Users.Username,
    userspice.users.password = mattermost_Aug6Backup.Users.Password,
    userspice.users.fname = mattermost_Aug6Backup.Users.FirstName,
    userspice.users.lname = mattermost_Aug6Backup.Users.LastName
FROM mattermost_Aug6Backup.Users
	INNER JOIN userspice.users ON userspice.users.username = mattermost_Aug6Backup.Users.Username
WHERE userspice.users.username = mattermost_Aug6Backup.Users.Username

AND (

userspice.users.email <> mattermost_Aug6Backup.Users.Email
OR userspice.users.username <> mattermost_Aug6Backup.Users.Username

OR userspice.users.password <> mattermost_Aug6Backup.Users.Password

OR userspice.users.fname <> mattermost_Aug6Backup.Users.FirstName

OR userspice.users.lname <> mattermost_Aug6Backup.Users.LastName

);

[ionbasa]

3 minutes ago, a2martinez said:

It sounds like you only want to update where they are not equal because you want the mattermost instead. Try this

 

UPDATE userspice.users 
SET
	userspice.users.email = mattermost_Aug6Backup.Users.Email,
    userspice.users.username = mattermost_Aug6Backup.Users.Username,
    userspice.users.password = mattermost_Aug6Backup.Users.Password,
    userspice.users.fname = mattermost_Aug6Backup.Users.FirstName,
    userspice.users.lname = mattermost_Aug6Backup.Users.LastName
FROM mattermost_Aug6Backup.Users
	INNER JOIN userspice.users ON userspice.users.username = mattermost_Aug6Backup.Users.Username
WHERE userspice.users.username  mattermost_Aug6Backup.Users.Username;

Yes, that is what I want. But it still doesn't work!

I get the same error message from MariaDB (MySQL):

Quote

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM mattermost_Aug6Backup.Users
    INNER JOIN userspice.users ON userspice.users' at line 8

The only thing I can see that differs between what I posted any posted was the '=' sign in the line of the query. I tried both with and without and it always comes up with the same issue. To the best of my knowledge, it should work, but isn't

[ionbasa]

20 minutes ago, a2martinez said:

First post I sent was wrong. You want the OR for not equal

 

So, this is what I understood from your post:

UPDATE userspice.users 
SET
	userspice.users.email = mattermost_Aug6Backup.Users.Email,
    userspice.users.username = mattermost_Aug6Backup.Users.Username,
    userspice.users.password = mattermost_Aug6Backup.Users.Password,
    userspice.users.fname = mattermost_Aug6Backup.Users.FirstName,
    userspice.users.lname = mattermost_Aug6Backup.Users.LastName
FROM mattermost_Aug6Backup.Users
	INNER JOIN userspice.users ON userspice.users.username = mattermost_Aug6Backup.Users.Username
WHERE userspice.users.username = mattermost_Aug6Backup.Users.Username
AND (
userspice.users.email <> mattermost_Aug6Backup.Users.Email
OR userspice.users.username <> mattermost_Aug6Backup.Users.Username
OR userspice.users.password <> mattermost_Aug6Backup.Users.Password
OR userspice.users.fname <> mattermost_Aug6Backup.Users.FirstName
OR userspice.users.lname <> mattermost_Aug6Backup.Users.LastName
);

But I still get the same error message. I have no Idea why this is causing so much issues.

 

EDIT: I think there is something wrong with the INNER JOIN or how it's being performed?

[a2martinez]

looks like with MySQL there is a different syntax you need to specify the join in your update clause, look at this article

 

https://stackoverflow.com/questions/2114534/mysql-syntax-for-join-update

 

UPDATE Reservations r JOIN Train t ON (r.Train = t.TrainID)

[ionbasa]

8 minutes ago, a2martinez said:

looks like with MySQL there is a different syntax you need to specify the join in your update clause, look at this article

 

https://stackoverflow.com/questions/2114534/mysql-syntax-for-join-update

 

UPDATE Reservations r JOIN Train t ON (r.Train = t.TrainID)

So, I should start off with something like this:

UPDATE userspice.users 
INNER JOIN userspice.users ON userspice.users.username = mattermost_Aug6Backup.Users.Username
SET
	userspice.users.email = mattermost_Aug6Backup.Users.Email,
    userspice.users.username = mattermost_Aug6Backup.Users.Username,
    userspice.users.password = mattermost_Aug6Backup.Users.Password,
    userspice.users.fname = mattermost_Aug6Backup.Users.FirstName,
    userspice.users.lname = mattermost_Aug6Backup.Users.LastName
FROM mattermost_Aug6Backup.Users

Which tells me that:

Quote

#1066 - Not unique table/alias: 'users'

 

[ionbasa]

Ok I somewhat have a start, the following query works:

UPDATE userspice.users, mattermost_Aug6Backup.Users 
SET 
	userspice.users.email = mattermost_Aug6Backup.Users.Email, 
	userspice.users.username = mattermost_Aug6Backup.Users.Username, 
	userspice.users.password = mattermost_Aug6Backup.Users.Password, 
	userspice.users.fname = mattermost_Aug6Backup.Users.FirstName, 
	userspice.users.lname = mattermost_Aug6Backup.Users.LastName 
WHERE userspice.users.username = mattermost_Aug6Backup.Users.Username

Would the inner join still be necessary? When I ran the query, I got zero rows affected. I'm going to test this out by inserting a few rows in the source table and re-running the command to see the output.

[a2martinez]

try this

 

UPDATE userspice.users U
INNER JOIN mattermost_Aug6Backup.Users M ON U.Users.username = M.Users.Username
SET
    U.users.email = M.Users.Email,
    U.users.username = M.Users.Username,
    U.users.[password] = M.Users.[Password],
    U.users.fname = M.Users.FirstName,
    U.users.lname = M.Users.LastName
FROM mattermost_Aug6Backup.Users AS M2
    INNER JOIN userspice.users AS U2 ON U2.users.username = M2.Users.Username
WHERE 
U2.users.email <> mattermost_Aug6Backup.Users.Email
OR U2.users.username <> M2.Users.Username
OR U2.users.[password] <> M2.Users.[Password]
OR U2.users.fname <> M2.Users.FirstName
OR U2.users.lname <> M2.Users.LastName
;

 

[a2martinez]

Whoa I just realized a huge problem with this, You are trying to change your unique identifier that you are joining on... Username... is that true?

[ionbasa]

Just now, a2martinez said:

Whoa I just realized a huge problem with this, You are trying to change your unique identifier that you are joining on... Username... is that true?

That would be an issue if a username changes. DOH.

mattermost.Users has a unique ID column. I could copy that into userspice.users. That way its impossible for the identifier to change.

 

I dont actually know if users can change their username, that would have to be something I need to investigate.

[a2martinez]

If you take out the line for the Username from the SET and WHERE clauses does it work?

[ionbasa]

27 minutes ago, a2martinez said:

If you take out the line for the Username from the SET and WHERE clauses does it work?

The last post you gave had two 'inner join' clauses, and thus gave another error.

I think I should start over from scratch. Running the following works:

UPDATE userspice.users, mattermost_Aug6Backup.Users 
SET 
	userspice.users.email = mattermost_Aug6Backup.Users.Email, 
	userspice.users.username = mattermost_Aug6Backup.Users.Username, 
	userspice.users.password = mattermost_Aug6Backup.Users.Password, 
	userspice.users.fname = mattermost_Aug6Backup.Users.FirstName, 
	userspice.users.lname = mattermost_Aug6Backup.Users.LastName 
WHERE userspice.users.username = mattermost_Aug6Backup.Users.Username

This works, assuming that the mattermost username does not change. That's something I am unsure about. Given this query, is INNER JOIN still needed?

[a2martinez]

It is only needed if you only want to update the records that have a change and not update all records that exist in both tables

[ionbasa]

2 minutes ago, a2martinez said:

It is only needed if you only want to update the records that have a change and not update all records that exist in both tables

Which is weird. If I run the following command, it only updates the affected rows, not the entire table.

For example. If I add 3 rows to Mattermost, and run the query, it shows "3 rows modified on Userspice".

[a2martinez]

I'm sorry we couldn't get the stuff above to work, I would start writing a query to identify the records that need to be updated and then nest that into an update