Re: Update Select

[laptop alias]

DROP TABLE IF EXISTS dbs;

CREATE TABLE dbs
(dt DATETIME NOT NULL
,interno INT NOT NULL
,numero VARCHAR(20) NOT NULL
,duracion TIME NOT NULL
,codigo VARCHAR(12) NULL
,PRIMARY KEY(interno,numero)
);

INSERT INTO dbs VALUES
('2012-07-04 15:10',104,'846359878','00:01:55','1234'),
('2012-07-04 15:12',104,'46359878','00:02:05',NULL);

SELECT * FROM dbs;
+---------------------+---------+-----------+----------+--------+
| dt                  | interno | numero    | duracion | codigo |
+---------------------+---------+-----------+----------+--------+
| 2012-07-04 15:12:00 |     104 | 46359878  | 00:02:05 | NULL   |
| 2012-07-04 15:10:00 |     104 | 846359878 | 00:01:55 | 1234   |
+---------------------+---------+-----------+----------+--------+

UPDATE dbs dr1,dbs dr2
   SET dr2.codigo = dr1.codigo
 WHERE dr1.numero LIKE '8%'
   AND MID(dr1.numero,2)=dr2.numero 
    AND dr1.interno = dr2.interno 
    AND DATE(dr1.dt) = DATE(dr2.dt) 
   AND TIME(dr1.dt) <= TIME(dr2.dt);
 
 
SELECT * FROM dbs;
+---------------------+---------+-----------+----------+--------+
| dt                  | interno | numero    | duracion | codigo |
+---------------------+---------+-----------+----------+--------+
| 2012-07-04 15:12:00 |     104 | 46359878  | 00:02:05 | 1234   |
| 2012-07-04 15:10:00 |     104 | 846359878 | 00:01:55 | 1234   |
+---------------------+---------+-----------+----------+--------+