How to UPDATE all columns of a record without having to list every column
It's not possible.
What you're trying to do is not part of SQL specification and is not supported by any database vendor. See the specifications of SQL UPDATE statements for MySQL, Postgresql, MSSQL, Oracle, Firebird, Teradata. Every one of those supports only below syntax:
UPDATE table_reference
SET column1 = {expression} [, column2 = {expression}] ...
[WHERE ...]
How to update all columns with INSERT ... ON CONFLICT ...?
The UPDATE
syntax requires to explicitly name target columns.
Possible reasons to avoid that:
- You have many columns and just want to shorten the syntax.
- You do not know column names except for the unique column(s).
"All columns"
has to mean "all columns of the target table" (or at least "leading columns of the table") in matching order and matching data type. Else you'd have to provide a list of target column names anyway.
Test table:
CREATE TABLE tbl (
id int PRIMARY KEY
, text text
, extra text
);
INSERT INTO tbl VALUES
(1, 'foo')
, (2, 'bar')
;
1. DELETE
& INSERT
in single query instead
Without knowing any column names except id
.
Only works for "all columns of the target table". While the syntax even works for a leading subset, excess columns in the target table would be reset to their respective column default (default NULL) with DELETE
and INSERT
.
UPSERT (INSERT ... ON CONFLICT ...
) is needed to avoid concurrency / locking issues under concurrent write load, and only because there is no general way to lock not-yet-existing rows in Postgres (value locking).
Your special requirement only affects the UPDATE
part. Possible complications do not apply where existing rows are affected. Those are locked properly. Simplifying some more, you can reduce your case to DELETE
and INSERT
:
WITH data(id) AS ( -- Only 1st column gets explicit name
VALUES
(1, 'foo_upd', 'a') -- changed
, (2, 'bar', 'b') -- unchanged
, (3, 'baz', 'c') -- new
)
, del AS (
DELETE FROM tbl AS t
USING data d
WHERE t.id = d.id
-- AND t <> d -- optional, to avoid empty updates
) -- only works for complete rows
INSERT INTO tbl AS t
TABLE data -- short for: SELECT * FROM data
ON CONFLICT (id) DO NOTHING
RETURNING t.id;
In the Postgres MVCC model, an UPDATE
is largely the same as DELETE
and INSERT
- except for some corner cases with concurrency, triggers, HOT updates, and big column values stored out of line, "TOASTed" values. Since you want to replace all rows anyway, just remove conflicting rows before the INSERT
. Deleted rows remain locked until the transaction is committed. The INSERT
might only find conflicting rows for previously non-existing key values if a concurrent transaction happens to insert them concurrently (after the DELETE
, but before the INSERT
).
You would lose additional column values for affected rows in this special case. No exception raised. But if competing queries have equal priority, that's hardly a problem: the other query won for some rows. Also, if the other query is a similar UPSERT, its alternative is to wait for this transaction to commit and then updates right away. "Winning" could be a Pyrrhic victory.
About "empty updates":
- How do I (or can I) SELECT DISTINCT on multiple columns?
No, my query must win!
OK, you asked for it:
WITH data(id) AS ( -- Only 1st column gets explicit name
VALUES -- rest gets default names "column2", etc.
(1, 'foo_upd', NULL) -- changed
, (2, 'bar', NULL) -- unchanged
, (3, 'baz', NULL) -- new
, (4, 'baz', NULL) -- new
)
, ups AS (
INSERT INTO tbl AS t
TABLE data -- short for: SELECT * FROM data
ON CONFLICT (id) DO UPDATE
SET id = t.id
WHERE false -- never executed, but locks the row!
RETURNING t.id
)
, del AS (
DELETE FROM tbl AS t
USING data d
LEFT JOIN ups u USING (id)
WHERE u.id IS NULL -- not inserted!
AND t.id = d.id
-- AND t <> d -- avoid empty updates - only for full rows
RETURNING t.id
)
, ins AS (
INSERT INTO tbl AS t
SELECT *
FROM data
JOIN del USING (id) -- conflict impossible!
RETURNING id
)
SELECT ARRAY(TABLE ups) AS inserted -- with UPSERT
, ARRAY(TABLE ins) AS updated; -- with DELETE & INSERT
How?
- The 1st CTE
data
just provides data. Could be a table instead. - The 2nd CTE
ups
: UPSERT. Rows with conflictingid
are not changed, but also locked. - The 3rd CTE
del
deletes conflicting rows. They remain locked. - The 4th CTE
ins
inserts whole rows. Only allowed for the same transaction - The final
SELECT
is optional, to show what happened.
To check for empty updates test (before and after) with:
SELECT ctid, * FROM tbl; -- did the ctid change?
The (commented out) check for any changes in the row AND t <> d
works even with NULL values because we are comparing two typed row values according to the manual:
two NULL field values are considered equal, and a NULL is considered larger than a non-NULL
But all columns must support =
/ <>
operators for the row comparison to work. See:
- How to query a json column for empty objects?
2. Dynamic SQL
This works for a subset of leading columns too, preserving existing values.
The trick is to let Postgres build the query string with column names from the system catalogs dynamically, and then execute it.
See related answers for code:
Update multiple columns in a trigger function in plpgsql
Bulk update of all columns
SQL update fields of one table from fields of another one
Updating one column in all rows in a table
You're over-complicating the solution. In order to update every record, the approach you're trying to take is:
- Select all records.
- Get the ID for each record.
- Loop through the IDs.
- Update each record by that ID.
The UPDATE
syntax has a much easier way to do this. You don't need to supply a WHERE
clause to an UPDATE
statement. Without that clause, it will by default update every record in the table:
UPDATE TableName SET `board`='value'
Also, please be aware that you have a SQL injection vulnerability in your code. By using $_REQUEST['text']
directly in your SQL query, you allow any user to send SQL code to your query. Your code then executes whatever they send you. This could allow them to corrupt or delete your data, even gain administrative access to your server.
For starters, please stop using mysql_*
functions. PHP has deprecated those and they should no longer be used. There is a mysqli_
replacement for them. In addition to that, use the mysqli_real_escape_string()
function to sanitize your inputs before using them in a SQL query. And finally, use prepared statements instead of directly concatenating values into your SQL string.
can I update all the columns in one set
There is no SQL syntax to give you a quick and easy way of doing this that I know of.
One way you could do it is to write a PL/SQL block to select the column names of a given table from a system view e.g. user_tab_cols and write them to a variable to build up the SQL code to run dynamically. To be honest, unless you have a number of tables to do this on or loads of columns it would probably be easier to write the query manually.
EDIT - Here is the code incase you want it
declare
v_table varchar2(50) := 'test_table';
v_sql varchar2(2000) := null;
cursor c_cols(p_table varchar2) is
select c.column_name as col
from user_tab_cols c
where c.table_name = upper(p_table)
order by c.column_id;
begin
-- write first line of sql...
v_sql := 'update ' || v_table || ' set' || chr(10);
-- loop through col names to add each col into update statement...
for l_c_cols in c_cols(v_table) loop
v_sql := v_sql || l_c_cols.col || ' = trim(' || l_c_cols.col || '),' || chr(10);
end loop;
-- remove last comma...
v_sql := substr(v_sql,1,length(v_sql)-2);
-- run dynamic sql...
dbms_output.put_line(v_sql);
begin
execute immediate v_sql;
commit;
end;
end;
Let me know if you have any questions on this.
How to select all the columns of a table except one column?
You can use this approach to get the data from all the columns except one:-
- Insert all the data into a temporary table
- Then drop the column which you dont want from the temporary table
- Fetch the data from the temporary table(This will not contain the data of the removed column)
- Drop the temporary table
Something like this:
SELECT * INTO #TemporaryTable FROM YourTableName
ALTER TABLE #TemporaryTable DROP COLUMN Columnwhichyouwanttoremove
SELECT * FROM #TemporaryTable
DROP TABLE #TemporaryTable
SQL - Update multiple records in one query
Try either multi-table update syntax
UPDATE config t1 JOIN config t2
ON t1.config_name = 'name1' AND t2.config_name = 'name2'
SET t1.config_value = 'value',
t2.config_value = 'value2';
Here is a SQLFiddle demo
or conditional update
UPDATE config
SET config_value = CASE config_name
WHEN 'name1' THEN 'value'
WHEN 'name2' THEN 'value2'
ELSE config_value
END
WHERE config_name IN('name1', 'name2');
Here is a SQLFiddle demo
How to update multiple columns having the same value or different values in SQL?
You can, by using a case expression, but whats the advantage?
update table_name set
col1 = case when col1 = -99 then null /* or any new value for col1 */ else col1 end
, col2 = case when col2 = -99 then null /* or any new value for col2 */ else col2 end
where col1 = -99 or col2 = -99;
Note, as pointed out by Larnu, when you are setting the column to null you can simplify the update to:
update table_name set
col1 = nullif(col1,-99)
, col2 = nullif(col2,-99)
where col1 = -99 or col2 = -99;
And you can change the values you are using (-99) on a per column basis to whatever you want e.g. col2 = 5
update table_name set
col1 = nullif(col1,-99)
, col2 = nullif(col2,5)
where col1 = -99 or col2 = 5;
Efficient way to update all rows in a table
The usual way is to use UPDATE:
UPDATE mytable
SET new_column = <expr containing old_column>
You should be able to do this is a single transaction.
Postgres: Update all columns from another table
You could delete and re-insert, if the two tables have the same columns in the same order. Assuming that all records in tableB match tableA:
delete from tableA
where id in (select id from tableB)
insert into tableA
select *
from tableB;
(If all records do not match, you could use a temporary table to keep the necessary ids.)
Generally, I oppose doing insert
without a column list. In some cases, it is tolerable -- such as when tableB
was created as a subset from tableB
using *
.
Related Topics
Get The Difference Between Two Dates Both in Months and Days in Sql
How Gremlin Query Same SQL Like for Search Feature
How to Get The Old, Free Version of Anjlab's SQL Profiler
Sql Query to Find The Duplicate Records
Authoritative SQL Standard Documentation
Compare Performance Difference of T-Sql Between and '<' '>' Operator
How to Deal with a 50Gb Large CSV File in R Language
Rails Activerecord - How to Fetch Records Between Two Dates
Create a Sqlite View Where a Row Depends on The Previous Row
Aggregate Hstore Column in Postresql
Writing a Function in SQL to Loop Through a Date Range in a Udf
Does Introducing Foreign Keys to MySQL Reduce Performance
Sql: How to Select a Max Value for Each Group Per Day
How to Retrieve The Identities of Rows That Were Inserted Through Insert...Select
How to Get Information About an Index and Table Owner in Oracle