Database Insert statement convert to Update

[TIM_M_91]

Hi there I was wondering if their is anyone who can convert this statement from 'INSERT' to 'UPDATE' as I cannot seem to do is.

Code can be seen below:

public static void COUNT2( ) {
        try {
ResultSet res = stmt.executeQuery("INSERT INTO TABLE2 (VIDEOID,VIDEONAME,DIRECTOR,RATING,PLAYCOUNT) SELECT VIDEOID,VIDEONAME,DIRECTOR,RATING,PLAYCOUNT FROM TABLE1" );
            while (res.next()) { // there is a result
               
            }
        } catch (Exception e) {
            System.out.println(e);
           
        }
    }

I have tried the following which produced the error Syntax error in UPDATE statement:

public static void COUNT2( ) {
        try {
            ResultSet res = stmt.executeQuery("UPDATE TABLE2 SET VIDEOID,VIDEONAME,DIRECTOR,RATING,PLAYCOUNT = VIDEOID,VIDEONAME,DIRECTOR,RATING,PLAYCOUNT FROM TABLE1 " );

            while (res.next()) { // there is a result
               // output += formatListEntry(res);
            }
        } catch (Exception e) {
            System.out.println(e);
           
        }
    }

[StephNicolaou]

Try changing the equals to VALUES()

E.G.

UPDATE TABLE2 SET VIDEOID,VIDEONAME,DIRECTOR,RATING,PLAYCOUNT VALUES ( 'VIDEOID','VIDEONAME','DIRECTOR','RATING','PLAYCOUNT') FROM TABLE1

Another UPDATE that is possible:

E.G.2

INSERT INTO TBL1 ( A, B, C )
SELECT tbl.[A], tbl.[B], tbl.[C]
FROM aTBL AS tbl

[TIM_M_91]

Try changing the equals to VALUES()

E.G.

UPDATE TABLE2 SET VIDEOID,VIDEONAME,DIRECTOR,RATING,PLAYCOUNT VALUES ( 'VIDEOID','VIDEONAME','DIRECTOR','RATING','PLAYCOUNT') FROM TABLE1

Another UPDATE that is possible:

E.G.2

INSERT INTO TBL1 ( A, B, C )
SELECT tbl.[A], tbl.[B], tbl.[C]
FROM aTBL AS tbl

Your example 1 still gave me an Error :

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

[TIM_M_91]

Hi guys what would I need to do to the following code. So not only does it update PLAYLIST Table but it also updates table_2 column PLAYCOUNT. I have tried a number of different statements but they all keep on producing errors any help?

ResultSet res = stmt.executeQuery("UPDATE PLAYLIST SET PLAYCOUNT = PLAYCOUNT + 1");

[StephNicolaou]

Okay great! You got the UPDATE statement to work without the apostrophies, that's what I would have tried next. Update statements work differently depending on the type of SQL so can never be sure, I used Access for my example.

For updating two tables I would suggest you to play with the following pseudo:

UPDATE PLAYLIST AS a, table_2 AS b
SET PLAYCOUNT = PLAYCOUNT + 1 / a.PLAYCOUNT = b.PLAYCOUNT
WHERE a.ID = b.ID