sql - update in subquery & same table

Question

I want to update multiple lines of data, but an error occurred.

Below is the table information and query statement.

myTable

ltiNum  ltiCd   updateDate  ClosedOrNot

1   A   2020-07-01  
2   B   2020-07-01  
3   C   2020-07-01  
4   D   2020-07-01  
5   E   2020-07-01  
1   A   2020-08-01  
3   C   2020-08-01  
4   D   2020-08-01  
5   E   2020-08-01  
6   F   2020-08-01

SQL

update myTable 
set closedOrNot = 
        (case when (SELECT CONCAT(ltiNum,ltiCd) FROM myTable where updateDate Like '2020-08%'
                    and CONCAT(ltiNum,ltiCd) in (SELECT CONCAT(ltiNum,ltiCd) FROM myTable WHERE updateDate Like '2020-07%'       
        )) then 'existing'
        when (SELECT CONCAT(ltiNum,ltiCd) FROM myTable where updateDate Like '2020-08%'
                    and CONCAT(ltiNum,ltiCd) not in (SELECT CONCAT(ltiNum,ltiCd) FROM myTable WHERE updateDate Like '2020-07%'
        )) then 'New'        
         when (SELECT CONCAT(ltiNum,ltiCd) FROM myTable where updateDate Like '2020-07%'
                    and CONCAT(ltiNum,ltiCd) not in (SELECT CONCAT(ltiNum,ltiCd) FROM myTable WHERE updateDate Like '2020-08%'
        )) then 'Closed'
    end);

Error code :

mysql error code 1093: You can't specify target table for 'myTable' update in FROM clause

Where is the problem?

i want see follow images

please help~!

enter image description here

I have the impression that you need to review your case statement. It seem that there is no condition and the CONCAT function is irrelevant. In other words, you-re saying -if a and b then do something regardless of the values of a and b. In regard to CONCAT you=re not joining anything, so you don't need CONCAT at all. Ypu may take advantage of some of the MySQL tutorials available on the net. — Erick
You want me to guess what you are trying to do based on non-working code? — P.Salmon
What do you want to achieve with this query? It looks like a bad idea to introduce a new column for something that a query can already tell you. Don't store data redundantly in a database. — Thorsten Kettner
You need to explain the logic that computes the statuses, otherwise it is hard to suggest relevant improvements for the query. — GMB

nbk nbk · Accepted Answer · 2020-09-06T14:26:24

You must encapsule the mytable with in a SELECT

update myTable 
set closedOrNot = 
        (case when (SELECT CONCAT(ltiNum,ltiCd) FROM (SELECT * FROM myTable) t1 where updateDate Like '2020-08%'
                    and CONCAT(ltiNum,ltiCd) in (SELECT CONCAT(ltiNum,ltiCd) FROM (SELECT * FROM myTable) t1 WHERE updateDate Like '2020-07%'       
        )) then 'existing'
        when (SELECT CONCAT(ltiNum,ltiCd) FROM (SELECT * FROM myTable) t1 where updateDate Like '2020-08%'
                    and CONCAT(ltiNum,ltiCd) not in (SELECT CONCAT(ltiNum,ltiCd) FROM (SELECT * FROM myTable) t1 WHERE updateDate Like '2020-07%'
        )) then 'New'        
         when (SELECT CONCAT(ltiNum,ltiCd) FROM (SELECT * FROM myTable) t1 where updateDate Like '2020-07%'
                    and CONCAT(ltiNum,ltiCd) not in (SELECT CONCAT(ltiNum,ltiCd) FROM (SELECT * FROM myTable) t1 WHERE updateDate Like '2020-08%'
        )) then 'Closed'
    end);

So mysql thinks, that this is another table, but you are basically changing all the rows, because you didn't use a WHERE clause, so that it could happen that further the road, the results could be changed

Basically everything is correct what o told you but your query has another problem

CREATE TABLE myTable (
  `ltiNum` INTEGER,
  `ltiCd` VARCHAR(1),
  `updateDate` VARCHAR(10),
  `closedOrNot` VARCHAR(8)
);

INSERT INTO myTable
  (`ltiNum`, `ltiCd`, `updateDate`, `closedOrNot`)
VALUES
  ('1', 'A', '2020-07-01', 'NULL'),
  ('2', 'B', '2020-07-01', 'Closed'),
  ('3', '0', '2020-07-01', 'NULL'),
  ('4', 'D', '2020-07-01', 'NULL'),
  ('5', 'E', '2020-07-01', 'NULL'),
  ('1', 'A', '2020-08-01', 'existing'),
  ('3', '0', '2020-08-01', 'existing'),
  ('4', 'D', '2020-08-01', 'existing'),
  ('5', 'E', '2020-08-01', 'existing'),
  ('6', 'F', '2020-08-01', 'New');

update myTable 
set closedOrNot = 
        (case when (SELECT CONCAT(ltiNum,ltiCd) FROM (SELECT * FROM myTable) t1 where updateDate Like '2020-08%'
                    and CONCAT(ltiNum,ltiCd) in (SELECT CONCAT(ltiNum,ltiCd) FROM (SELECT * FROM myTable) t1 WHERE updateDate Like '2020-07%'       
        )) then 'existing'
        when (SELECT CONCAT(ltiNum,ltiCd) FROM (SELECT * FROM myTable) t1 where updateDate Like '2020-08%'
                    and CONCAT(ltiNum,ltiCd) not in (SELECT CONCAT(ltiNum,ltiCd) FROM (SELECT * FROM myTable) t1 WHERE updateDate Like '2020-07%'
        )) then 'New'        
         when (SELECT CONCAT(ltiNum,ltiCd) FROM (SELECT * FROM myTable) t1 where updateDate Like '2020-07%'
                    and CONCAT(ltiNum,ltiCd) not in (SELECT CONCAT(ltiNum,ltiCd) FROM (SELECT * FROM myTable) t1 WHERE updateDate Like '2020-08%'
        )) then 'Closed'
    end);

Subquery returns more than 1 row

 SELECT CONCAT(ltiNum,ltiCd) 
 FROM (SELECT * FROM myTable) t1 
 where updateDate Like '2020-08%'
                    and CONCAT(ltiNum,ltiCd) in (SELECT CONCAT(ltiNum,ltiCd) FROM (SELECT * FROM myTable) t1 WHERE updateDate Like '2020-07%' )

| CONCAT(ltiNum,ltiCd) |
| :------------------- |
| 1A                   |
| 30                   |
| 4D                   |
| 5E                   |

SELECT CONCAT(ltiNum,ltiCd) FROM (SELECT * FROM myTable) t1 where updateDate Like '2020-08%'
                    and CONCAT(ltiNum,ltiCd) not in (SELECT CONCAT(ltiNum,ltiCd) FROM (SELECT * FROM myTable) t1 WHERE updateDate Like '2020-07%'
        )

| CONCAT(ltiNum,ltiCd) |
| :------------------- |
| 6F                   |

SELECT CONCAT(ltiNum,ltiCd) FROM (SELECT * FROM myTable) t1 where updateDate Like '2020-07%'
                    and CONCAT(ltiNum,ltiCd) not in (SELECT CONCAT(ltiNum,ltiCd) FROM (SELECT * FROM myTable) t1 WHERE updateDate Like '2020-08%'
        )

| CONCAT(ltiNum,ltiCd) |
| :------------------- |
| 2B                   |

db<>fiddle here

As you can see the first CASE retursn 4 results, so that your construction doesn't work-

sql - update in subquery & same table

1 Answers