Using MYSQL 5.7.21 and WorkBench 6.3 and need to delete all players who live in

ID: 3731793 • Letter: U

Question

Using MYSQL 5.7.21 and WorkBench 6.3 and need to delete all players who live in the same town as player 57 but keep the data about player 57 (you may need to alias the sub-query on the town name for 57). Have written the following, but in DELETE statement get, Syntax error: unexpected '@TOWN_57' (at text suffix), with @TOWN_57 underlined before execution.

SET @TOWN_57 = (SELECT TOWN

FROM PLAYERS

WHERE PLAYERNO = '57');

DELETE
FROM PLAYERS
WHERE TOWN in (SELECT TOWN
        FROM PLAYERS
        WHERE PLAYERNO = '57')
        AND PLAYERNO NOT IN @TOWN_57;

USE TENNIS;

DROP TABLE PLAYERS;
DROP TABLE TEAMS;
DROP TABLE MATCHES;
DROP TABLE PENALTIES;
DROP TABLE COMMITTEE_MEMBERS;
CREATE TABLE PLAYERS
(PLAYERNO INTEGER NOT NULL,
NAME CHAR(15) NOT NULL,
INITIALS CHAR(3) NOT NULL,
BIRTH_DATE DATE ,
SEX CHAR(1) NOT NULL,
JOINED SMALLINT NOT NULL,
STREET VARCHAR(30) NOT NULL,
HOUSENO CHAR(4) ,
POSTCODE CHAR(6) ,
TOWN VARCHAR(30) NOT NULL,
PHONENO CHAR(13) ,
LEAGUENO CHAR(4) ,
PRIMARY KEY (PLAYERNO) );
CREATE TABLE TEAMS
(TEAMNO INTEGER NOT NULL,
PLAYERNO INTEGER NOT NULL,
DIVISION CHAR(6) NOT NULL,
PRIMARY KEY (TEAMNO) );
CREATE TABLE MATCHES
(MATCHNO INTEGER NOT NULL,
TEAMNO INTEGER NOT NULL,
PLAYERNO INTEGER NOT NULL,
WON SMALLINT NOT NULL,
LOST SMALLINT NOT NULL,
PRIMARY KEY (MATCHNO) );
CREATE TABLE PENALTIES
(PAYMENTNO INTEGER NOT NULL,
PLAYERNO INTEGER NOT NULL,
PAYMENT_DATE DATE NOT NULL,
AMOUNT DECIMAL(7,2) NOT NULL,
PRIMARY KEY (PAYMENTNO) );
CREATE TABLE COMMITTEE_MEMBERS
(PLAYERNO INTEGER NOT NULL,
BEGIN_DATE DATE NOT NULL,
END_DATE DATE ,
POSITION CHAR(20) ,
PRIMARY KEY (PLAYERNO, BEGIN_DATE));

INSERT INTO PLAYERS VALUES (
2, 'Everett', 'R', '1948-09-01', 'M', 1975, 'Stoney Road',
'43', '3575NH', 'Stratford', '070-237893', '2411');
INSERT INTO PLAYERS VALUES (
6, 'Parmenter', 'R', '1964-06-25', 'M', 1977, 'Haseltine Lane',
'80', '1234KK', 'Stratford', '070-476537', '8467');
INSERT INTO PLAYERS VALUES (
7, 'Wise', 'GWS', '1963-05-11', 'M', 1981, 'Edgecombe Way',
'39', '9758VB', 'Stratford', '070-347689', NULL);
INSERT INTO PLAYERS VALUES (
8, 'Newcastle', 'B', '1962-07-08', 'F', 1980, 'Station Road',
'4', '6584WO', 'Inglewood', '070-458458', '2983');
INSERT INTO PLAYERS VALUES (
27, 'Collins', 'DD', '1964-12-28', 'F', 1983, 'Long Drive',
'804', '8457DK', 'Eltham', '079-234857', '2513');
INSERT INTO PLAYERS VALUES (
28, 'Collins', 'C', '1963-06-22', 'F', 1983, 'Old Main Road',
'10', '1294QK', 'Midhurst', '010-659599', NULL);
INSERT INTO PLAYERS VALUES (
39, 'Bishop', 'D', '1956-10-29', 'M', 1980, 'Eaton Square',
'78', '9629CD', 'Stratford', '070-393435', NULL);
INSERT INTO PLAYERS VALUES (
44, 'Baker', 'E', '1963-01-09', 'M', 1980, 'Lewis Street',
'23', '4444LJ', 'Inglewood', '070-368753', '1124');
INSERT INTO PLAYERS VALUES (
57, 'Brown', 'M', '1971-08-17', 'M', 1985, 'Edgecombe Way',
'16', '4377CB', 'Stratford', '070-473458', '6409');
INSERT INTO PLAYERS VALUES (
83, 'Hope', 'PK', '1956-11-11', 'M', 1982, 'Magdalene Road',
'16A', '1812UP', 'Stratford', '070-353548', '1608');
INSERT INTO PLAYERS VALUES (
95, 'Miller', 'P', '1963-05-14', 'M', 1972, 'High Street',
'33A', '5746OP', 'Douglas', '070-867564', NULL);
INSERT INTO PLAYERS VALUES (
100, 'Parmenter', 'P', '1963-02-28', 'M', 1979, 'Haseltine Lane',
'80', '6494SG', 'Stratford', '070-494593', '6524');
INSERT INTO PLAYERS VALUES (
104, 'Moorman', 'D', '1970-05-10', 'F', 1984, 'Stout Street',
'65', '9437AO', 'Eltham', '079-987571', '7060');
INSERT INTO PLAYERS VALUES (
112, 'Bailey', 'IP', '1963-10-01', 'F', 1984, 'Vixen Road',
'8', '6392LK', 'Plymouth', '010-548745', '1319');
INSERT INTO TEAMS VALUES (1, 6, 'first');
INSERT INTO TEAMS VALUES (2, 27, 'second');
INSERT INTO MATCHES VALUES ( 1, 1, 6, 3, 1);
INSERT INTO MATCHES VALUES ( 2, 1, 6, 2, 3);
INSERT INTO MATCHES VALUES ( 3, 1, 6, 3, 0);
INSERT INTO MATCHES VALUES ( 4, 1, 44, 3, 2);
INSERT INTO MATCHES VALUES ( 5, 1, 83, 0, 3);
INSERT INTO MATCHES VALUES ( 6, 1, 2, 1, 3);
INSERT INTO MATCHES VALUES ( 7, 1, 57, 3, 0);
INSERT INTO MATCHES VALUES ( 8, 1, 8, 0, 3);
INSERT INTO MATCHES VALUES ( 9, 2, 27, 3, 2);
INSERT INTO MATCHES VALUES (10, 2, 104, 3, 2);
INSERT INTO MATCHES VALUES (11, 2, 112, 2, 3);
INSERT INTO MATCHES VALUES (12, 2, 112, 1, 3);
INSERT INTO MATCHES VALUES (13, 2, 8, 0, 3);
INSERT INTO PENALTIES VALUES (1, 6, '1980-12-08',100);
INSERT INTO PENALTIES VALUES (2, 44, '1981-05-05', 75);
INSERT INTO PENALTIES VALUES (3, 27, '1983-09-10',100);
INSERT INTO PENALTIES VALUES (4,104, '1984-12-08', 50);
INSERT INTO PENALTIES VALUES (5, 44, '1980-12-08', 25);
INSERT INTO PENALTIES VALUES (6, 8, '1980-12-08', 25);
INSERT INTO PENALTIES VALUES (7, 44, '1982-12-30', 30);
INSERT INTO PENALTIES VALUES (8, 27, '1984-11-12', 75);
INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1990-01-01', '1990-12-31', 'Secretary');
INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1991-01-01', '1992-12-31', 'Member');
INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1992-01-01', '1993-12-31', 'Treasurer');
INSERT INTO COMMITTEE_MEMBERS VALUES ( 6, '1993-01-01', NULL, 'Chairman');
INSERT INTO COMMITTEE_MEMBERS VALUES ( 2, '1990-01-01', '1992-12-31', 'Chairman');
INSERT INTO COMMITTEE_MEMBERS VALUES ( 2, '1994-01-01', NULL, 'Member');
INSERT INTO COMMITTEE_MEMBERS VALUES (112, '1992-01-01', '1992-12-31', 'Member');
INSERT INTO COMMITTEE_MEMBERS VALUES (112, '1994-01-01', NULL, 'Secretary');
INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1990-01-01', '1990-12-31', 'Treasurer');
INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1991-01-01', '1991-12-31', 'Secretary');
INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1993-01-01', '1993-12-31', 'Member');
INSERT INTO COMMITTEE_MEMBERS VALUES ( 8, '1994-01-01', NULL, 'Member');
INSERT INTO COMMITTEE_MEMBERS VALUES ( 57, '1992-01-01', '1992-12-31', 'Secretary');
INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1990-01-01', '1990-12-31', 'Member');
INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1991-01-01', '1991-12-31', 'Treasurer');
INSERT INTO COMMITTEE_MEMBERS VALUES ( 27, '1993-01-01', '1993-12-31', 'Treasurer');
INSERT INTO COMMITTEE_MEMBERS VALUES ( 95, '1994-01-01', NULL, 'Treasurer');
show tables;

Explanation / Answer

Note 1.

SET @TOWN_57 = (SELECT TOWN

FROM PLAYERS

WHERE PLAYERNO = '57');

In MySQL, the user defined variable can hold only one row. So, it is not useful for the given requirement.

Note 2.

In MySQL, you can't modify the same table which you use in the SELECT statement.

DELETE
FROM PLAYERS
WHERE TOWN in (SELECT TOWN
        FROM PLAYERS
        WHERE PLAYERNO = '57')
        AND PLAYERNO NOT IN @TOWN_57;

Recommended answer for the given requirement is

DELETE FROM PLAYERS WHERE TOWN in (select c.TOWN from (SELECT TOWN FROM PLAYERS WHERE PLAYERNO = '57') as c) and PLAYERNO not in ('57');

Here, If we use alias names for retrieving the values from same table we won't get any issues. Here we have used alias 'c'.

Result

Before query execution the data in PLAYERS table is:

2 Everett R 1948-09-01 M 1975 Stoney Road 43 3575NH Stratford 070-237893 2411

6 Parmenter R 1964-06-25 M 1977 Haseltine Lane 80 1234KK Stratford 070-476537 8467

7 Wise GWS 1963-05-11 M 1981 Edgecombe Way 39 9758VB Stratford 070-347689 NULL

8 Newcastle B 1962-07-08 F 1980 Station Road 4 6584WO Inglewood 070-458458 2983

27 Collins DD 1964-12-28 F 1983 Long Drive 804 8457DK Eltham 079-234857 2513

28 Collins C 1963-06-22 F 1983 Old Main Road 10 1294QK Midhurst 010-659599 NULL

39 Bishop D 1956-10-29 M 1980 Eaton Square 78 9629CD Stratford 070-393435 NULL

44 Baker E 1963-01-09 M 1980 Lewis Street 23 4444LJ Inglewood 070-368753 1124

57 Brown M 1971-08-17 M 1985 Edgecombe Way 16 4377CB Stratford 070-473458 6409

83 Hope PK 1956-11-11 M 1982 Magdalene Road 16A 1812UP Stratford 070-353548 1608

95 Miller P 1963-05-14 M 1972 High Street 33A 5746OP Douglas 070-867564 NULL

100 Parmenter P 1963-02-28 M 1979 Haseltine Lane 80 6494SG Stratford 070-494593 6524

104 Moorman D 1970-05-10 F 1984 Stout Street 65 9437AO Eltham 079-987571 7060

112 Bailey IP 1963-10-01 F 1984 Vixen Road 8 6392LK Plymouth 010-548745 1319

After query execution the data in PLAYERS table is: