using field data for another field in the same table


#1

Hi All!
I have a wierd one for you…

i have an existing datatable that i need to “copy” 1 field to another field within the same table… what would the easiest way be to accomplish this? … i would like to just use an update query if at all possible.

Lets say my table looks like this …

id name asset model
1 NULL h1000 VX10

i want to take the data from the ‘asset’ field and copy it to the name field

result would be
id name asset model
1 h1000 h1000 VX10


#2

It will be along the lines of…
(Add conditions as/if needed)

UPDATE table SET name = (SELECT asset FROM table)

#3

yea, i tried that and get the following error,

Using :UPDATE `assets` SET name = (SELECT asset_tag FROM `assets`) WHERE name IS NULL
Errors as:

Error: 1093: You can't specify target table 'assets' for update in FROM clause

#4

If all you are doing is setting the name column to the asset column, where the name column is null, all you need is -

UPDATE assets SET name = asset WHERE name IS NULL

If you are trying to do something else, you need to post a better example.


#5

Important info on this subject.

MariaDB starting with 10.3.2 From MariaDB 10.3.2, UPDATE statements may have the same source and target.

https://mariadb.com/kb/en/library/update/#update-statements-with-the-same-source-and-target


#6

Ty both, that helped alot.