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

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.

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?

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.