CODEWITHSUNDEEP
mysqlsqlsql-updatesql-insert
Alan
Alan

Reputation: 311

UPDATE statement for MySQL with specified fields

I have a array that I am inserting into a database with an INSERT statement like below.

INSERT INTO import_io 
(`total`, `thru`, `name/_source`, `name`, `name/_text`, `strokes`) 
VALUES ('-6','F','/golf/pga/players/Marc+Leishman/8442/scorecard/2014/35','http://sports.yahoo.com/golf/pga/players/Marc+Leishman/8442/scorecard/2014/35','Marc Leishman','64')

Is there anyway to use the UPDATE statement instead of an INSERT with having to split up the values so that I can go something like...

UPDATE import_io 
SET (`total`, `thru`, `name/_source`, `name`, `name/_text`, `strokes`) 
VALUES ('-6','F','/golf/pga/players/Marc+Leishman/8442/scorecard/2014/35','http://sports.yahoo.com/golf/pga/players/Marc+Leishman/8442/scorecard/2014/35','Marc Leishman','64') 
WHERE 'name/_text' = 'Marc Leishman'

Upvotes: 0

Views: 67

Answers (4)

Guy Goldenberg
Guy Goldenberg

Reputation: 819

You can fetch the array using SELECT modify it and the use UPDATE to update the column.

Since I don't know the programming language that you are using I can not give you an example.

Upvotes: 0

spencer7593
spencer7593

Reputation: 108450

Q: Is there anyway to use an UPDATE statement similar to this form:

UPDATE mytable 
   SET (col1, col2)
VALUES ('val1', 'val2')
 WHERE somecol = 'someval'

A: No, there is no similar syntax for an UPDATE statement.

The syntax of the UPDATE statement is very specific. There's no need to repeat the documentation here. Ref: http://dev.mysql.com/doc/refman/5.5/en/update.html

It's not clear what you are trying to achieve. So I hesitate to recommend INSERT ... ON DUPLICATE KEY UPDATE ..., though this is a useful pattern for "refreshing" a table from a source file (if there's a UNIQUE KEY in the table, and a value for the UNIQUE KEY provided in the source.)

I also hesitate to recommend loading a temporary table using INSERT, and then subsequently running a multi-table UPDATE statement to update the target table from the temporary table.

Upvotes: 1

user3780921
user3780921

Reputation:

To insert:

INSERT INTO Table_name (the_names_of_variebles) VALUES (In_Values_in_the_same_pattern)

To import:

SELECT * FROM Table_name WHERE variable_name = 'input_to_find'

Upvotes: 0

derdida
derdida

Reputation: 14904

That is not possible.

Other solutions: Create your Query String with PHP, and Use an Array of Cell Name and Content to Update or Insert. Build the Query String automatically.

Or Use "on duplicate key" to combine Insert or Update

insert into 
import_io (total, thru) 
VALUES('$total', '$thru') 
on duplicate key 
    update set total='$total
    ....

Upvotes: 0

Related Questions