Table and row replace

[Thurston_1]

I have written some SQL code which does not appear to work. Any pointers will be provided

1. Update Table 1 Column 1 with the value of Table 2 Column 1
2. Update Table 1 Column 2 with the value of Table 3 Column 1
3. Update must be based on the fact that a value in Table 2 Column 2 (e.g. 62-004) is in Table 3 Column 2 (62-004.png)

I used the following SQL statement, which does not appear to work, apparently there is an unknown column:

UPDATE t1Set t1.c1 = t2.c1, t1.c2 = t3.c1;
`Where t2.c2 in t3.c2

Any guidance will be appreciated.

Kind Regards

[hericles]

You have no select statement for table t2 or t3. You need to expand you where statement to actually get the data from t2 and t3.

[Thurston_1]

Hi Hericles

Thanks for responding.

I have now added the SELECT statement as follows:

UPDATE t1 
Set t1.c1 = t2.c1, t1.c2 = t3.c1;
Where t2.c2 in
(select c2
from t3)

However, it still gives errors.

Can you please provide assistance?

[AndrisP]

You selected variables from t2 and t3 by the where clause, but thear miss WHERE clause to table t1

[AndrisP]

You can use stored procedure e.g.

DROP PROCEDURE IF EXISTS `myupdate`;

DELIMITER $$
CREATE PROCEDURE `myupdate`()
BEGIN
    DECLARE var1 VARCHAR(10);
    DECLARE var2 VARCHAR(10);
    DECLARE e BOOLEAN DEFAULT TRUE;
    DECLARE mycurs CURSOR FOR SELECT t2.c1, t3.c1 FROM t2, t3 WHERE t2.c2 = t3.c2;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET e = FALSE;
    MP: BEGIN
        OPEN mycurs;
        WHILE e DO
            IF e THEN
                FETCH mycurs INTO var1, var2;
                INSERT INTO t1 (c1, c2) VALUES (var1, var2);
            END IF;
        END WHILE;
        CLOSE mycurs;
    END MP;
  COMMIT;
END;
$$

CALL `myupdate`();

but this example is for INSERT. If you need update then replace line INSERT to UPDATE with WHERE clause