Update inner join only if not null

Databases
1

Hi,
I’ve got the following update query:


UPDATE comunications,
                       files
            INNER JOIN user_join
                    ON user_join.file_id = files.file_id
                   SET files.file_status = 1,
                       comunications.post_status = 1
                 WHERE comunications.post_id = 18
                   AND user_join.post_id = comunications.post_id

This is my join table:

Schermata 2021-01-17 alle 07.58.11

The query works fine to update the file in the files table if file_id is not null, but not when file_id is null so, how can adapt this query to update the value in the files table only if file_id is not null? This is what I’ve tried so far:


UPDATE comunications,
                       files
            INNER JOIN user_join
                    ON user_join.file_id = files.file_id
                    
		
                       SET     files.file_status = IF(user_join.file_id IS NOT NULL, 1, files.file_status),
							   comunications.post_status = 1
			
                 WHERE comunications.post_id = 18
                   AND user_join.post_id = comunications.post_id
2

Back of my head’s saying files.file_status = CASE WHEN user_join.file_id IS NULL THEN files.file_status ELSE 1 END
It’s also 5 am. The back of my head is also questioning why an inner join would have NULL in an ID field.

3

Hi @m_hutley thanks for your reply, if I try what you sugested then I get the following error:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect

I can make it work with two queries a select query first (to check if user_join.user_id is null or not) and then an update query.

The reason why the user_join.file_id is null is because I use the user_join table to connet a many to many relation between the comunications and the files tables and because the comunication can have a file attached or not

4 
UPDATE comunications,
                       files

you lost me right here

why would you not write this using explicit JOIN syntax?

5

the best practice way to do this is to let the communication have a row in the many-to-many table or not

6

Hi the problem is what happen if the communication is shared with many users? Then I’ll have multiple duplicated rows in the communication table.

7

because if a communication doesn’t have a file, there wouldn’t be a row for that communication/file combo in the many-to-many table

and you’d be using an outer join

8

Hi yes I understand but I need to share the same communication with more than one user.

9

the relationship between communications and users should not be entangled with the relationship between communications and files

can you change your design? honestly, your SQL will get a lot simpler

10

Hi could you give me an example on ho structure the database in a better way please?

I could change it. It is extra work but well spent if makes life easier :slight_smile:

11 
CREATE TABLE communication_users 
( post_id INTEGER NOT NULL
, user_id INTEGER NOT NULL 
, PRIMARY KEY ( post_id, user_id )
);
CREATE TABLE communication_files 
( post_id INTEGER NOT NULL
, file_id INTEGER NOT NULL 
, PRIMARY KEY ( post_id, file_id )
);

note that all columns are NOT NULL – you don’t need any rows to show information that isn’t there

not sure what buildings have to do with this, but…

12

Hi @r937 thanks for your answer I understand now, so basically I should create two join tables. Just a quick thought if the post_id is primary key doesn’t mean it can’t accept duplicates? What happen in a situation where the same post (same ID) is shared with two users?

13

that is what it would mean, yes

but post_id is not the PK – it’s in a composite PK along with another column

so it’s the combination of values that’s unique

that means you cannot have the same file with the same post more than once

14

Oh great I didn’t know about composite PK. Many thanks for your help :slight_smile:

15

Hi @r937 sorry to trouble you again, I forgot to ask another question. When I create a new communication I can share the communication with one or many users but also with a group of users for example if I have some users with client role and some users with seller role and I want to share the communication with all the sellers and just one or more clients, how can I manage this situation? You can see in the image I’ve posted some user_id are NULL Do I need a third join table? Many thanks

16

depends entirely on whether you have an actual database structure for user groups

in which case your communications-to-users many-to-many table would link communications to user structure

google supertables and subtables for more information

for simplicity, i would consider just linking communications to individual users, and generating the users list at the time of communication – that way, when the user structure changes (and it will), you have an accurate picture of who got the communication, not who’s in the structure now

17

Hi @r937 so far I’ve got a table with all the roles and then a user_role table wich is a join table with the columns (user_id and role_id).

This is possible, but what appen if I register a new user with seller role then he will not be able to see the comunications that I’ve shared with all the sellers.

18

what would you like to happen?

19

I would like if I create a new user with seller role then he will be able to see all communications already shared with other sellers and also all comunications shared for him (obiously these will not be in the database yet) I’ve come up with the following schema but not sure if it will work, (sorry it is a hand sketch)

IMG_4284
IMG_42842674×3710 1.16 MB

20

neat

let me know when you get it implemented