Updating Records From Query

[PCHomepage]

I can't seem to work my brain around how to add a second WHERE to a query without breaking it. I am trying to update some records in one table from a different table. The INSERT is working; it's the UPDATE that doesn't look right.

Also, is it possible to be sure that there are no left-overs with the given value that are not being updated by removing them or would it be better to first remove them all, then just simply do an INSERT?

if (!isset($_SESSION['FileExists'])):
     $Query = "INSERT INTO table1 
			(FileID,
			Column2,
			Column3)
		SELECT UpdateID, Column2, Column3
		FROM table2
		WHERE Column1 NOT LIKE '%text%'
else:
     $Query = "UPDATE INTO table1 
			SET (Column2,
			Column3) =
		(SELECT Column2, Column3
		FROM table2
		WHERE Column1 NOT LIKE '%text%')
                WHERE FileID = 1;
endif;

 

[jpadie]

firstly UPDATE query syntax is UPDATE

. No INTO
second, I don't think you mysql has an UPDATE ... SELECT syntax. Instead I believe that you have to use a JOIN instead. The join could, I suppose, be late although I am not a fan of late binding.

UPDATE INTO table1 t1[COLOR=#990000],[/color] table2 t2
SET  
   t1[COLOR=#990000].[/color]Column2 [COLOR=#990000]=[/color] t2[COLOR=#990000].[/color]Column2
   t1[COLOR=#990000].[/color]Column3 [COLOR=#990000]=[/color] t2[COLOR=#990000].[/color]Column3
WHERE t2[COLOR=#990000].[/color]Column1 NOT LIKE [COLOR=#FF0000]'%text%'[/color] 
AND t2[COLOR=#990000].[/color]FileID [COLOR=#990000]=[/color] [COLOR=#993399]1[/color]

but you'd be better off asking in the mysql forum. a php solution would be very straightforward but would take two queries. nevertheless, sometimes two queries are quicker than one (more optimal) if the mysql engine needs to create many temporary indices and hold big join tables in memory. And sometimes for low frequency database administration, I find that lazy coding of sub-optimal queries is hardly a major performance penalty whereas, late at night after a bottle of wine it can take me hours to get a complex query right ...

[COLOR=#009900]$sql[/color] [COLOR=#990000]=[/color] [COLOR=#FF0000]'Select column2, column3 from table2 where column1 not like '[/color][COLOR=#009900]%text[/color][COLOR=#990000]%[/color][COLOR=#FF0000]' and fileID =1 LIMIT 1'[/color][COLOR=#990000];[/color]
[COLOR=#009900]$result[/color] [COLOR=#990000]=[/color] [b][COLOR=#000000]mysql_query[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$sql[/color][COLOR=#990000]);[/color]
[COLOR=#009900]$row[/color] [COLOR=#990000]=[/color] [b][COLOR=#000000]mysql_fetch_array[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$result[/color][COLOR=#990000],[/color] MYSQL_NUM[COLOR=#990000]);[/color]
[COLOR=#009900]$sql[/color] [COLOR=#990000]=[/color] [COLOR=#FF0000]"Update table1 set column2='%s', column3='%s'"[/color][COLOR=#990000];[/color]
[COLOR=#009900]$query[/color] [COLOR=#990000]=[/color] [b][COLOR=#000000]vsprintf[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$sql[/color][COLOR=#990000],[/color] [b][COLOR=#000000]array_may[/color][/b][COLOR=#990000]([/color][COLOR=#FF0000]'mysql_real_escape_string'[/color][COLOR=#990000],[/color] [COLOR=#009900]$row[/color][COLOR=#990000]));[/color]
[b][COLOR=#000000]mysql_query[/color][/b][COLOR=#990000]([/color][COLOR=#009900]$query[/color][COLOR=#990000])[/color] [b][COLOR=#0000FF]or[/color][/b] [b][COLOR=#0000FF]die[/color][/b] [COLOR=#990000]([/color][b][COLOR=#000000]mysql_error[/color][/b][COLOR=#990000]());[/color]

(although better these days to use PDO, so the manual say ...)

 

[PCHomepage]

Thank you. The more I think about it, the more I believe I should first simply delete the data, then run the same insert query to insert it because I am unsure if the number of lines will always be the same. I'm using the mysqli object oriented variation and also read up on PDO last night but haven't tried it.

 

[jpadie]

look up REPLACE syntax.

 

[PCHomepage]

Yes, I thought of that but since I am unsure if the number of rows match, I realized that it's better to remove and reinsert them. The possibily of the number of rows being different didn't cross my mind when I posted.