MySQL UPDATE SET query

# I have the following table:

CREATE TABLE RES (S int, R int, Q int, P int, RES varchar(100), M int );

# With the following contents:

INSERT INTO RES (S,R,Q,P,RES,M) VALUES

(1,1,1,20,"London",1),

(1,1,1,20,"Paris",0),

(1,1,1,20,"London",NULL),

(1,1,1,32,"Paris",NULL),

(1,1,1,11,"Berlin",NULL),

(1,1,2,20,"Newton",1),

(1,1,2,32,"Newton",1),

(1,1,2,32,"Isaac",0),

(1,1,2,11,"Isaac",NULL),

(1,1,2,11,"Newton",NULL),

(1,1,2,11,"Isaac Newton",NULL);

# I want a short UPDATE SET query to populate the NULL values

# after establishing rules indicated by the M field.

# For example, where S=1 and Q=1 and R=1 then we can establish that

# Where RES=London, M=1 and where RES=Paris, M=0

# I understand it needs to be an UPDATE SET statement with a subquery.

# NULLs should be retained where a rule cannot be established.

# The table is built in such a way that there will never be a conflict

# e.g. for RES=London, M will always equal 0 or NULL where S=1 and Q=1 and R=1

# The query should not reference specific values for any field

# The query needs to update all the NULLs where it can,

# and leave the NULLs as they are where it can't.

# the P field is not relevant, but is included to show there is no primary key.

# Secondly, I have the following table

CREATE TABLE QUA (S int, R int, Q int, ANS varchar(100));

# With the following contents:

INSERT INTO QUA (S,R,Q,ANS) VALUES

(1,1,1,"London"),

(1,1,2,"Isaac Newton");

# This establishes rules for populating the RES table with 1s in the M column.

# Where RES.M is NULL, a second UPDATE SET query should update the RES table.

# It should leave other NULLs as they are.

# Please provide two query lines that can be run successfully on [login to view URL]

# Many thanks.

MySQL Oracle SQL

ID Projek: #4098734

Tentang projek