New answers tagged insert
2
To summarize and close (at least for now) this question:
Original issue stems from the fact, that MySQL does not guarantee valid and consistent comparison of BIGINT against strings (VARCHAR or not) as confirmed in https://bugs.mysql.com/bug.php?id=103992
PHP currently does not support BIGINT. And even if getting such values as strings can be acceptable (at ...
3
The ON CONFLICT clause is strictly for handling unique violation and exclusion constraint violation errors, as documented in the manual. There is no similar syntax allowing you to handle foreign key violation errors.
You can ignore invalid references by incorporating checks into your insert statement, although you will need to switch from INSERT...VALUES to ...
1
SET autocommit = ON;
INSERT ...
pause
SELECT ...
During the 'pause' some other thread could DELETE or UPDATE that row. In this case, the SELECT would not find it.
BEGIN;
INSERT ...
pause
SELECT ...
COMMIT;
Now, the INSERT and SELECT are part of the same transaction. No other thread can mess with row in question. The SELECT will find the row. (There is ...
1
Sorry, I don't fully follow your question, if you could clarify it a little bit that would be helpful, regardless it sounds like you're looking for a query like this:
INSERT INTO aspnet_UsersAddition
(UserId, firstName, LastName, OrganizationID)
SELECT
UserId, '', '', 1
FROM aspnet_Membership
WHERE Email = 'admin@admin.com'
This will INSERT the ...
2
First of all, if you want to check both the b and the c rows against a, you should have a WHERE clause after each SELECT. Currently you are checking only the c rows, because the WHERE at the end of the query applies to the second UNION leg only.
Therefore, your query should probably be amended like this:
INSERT INTO a (column1, column2)
SELECT column1, ...
0
Though what you are asking is not a good practice, still if it is your business requirement then try this hack using common table expression.
As you want to avoid the sequence skip and reordering of records, so in first step we will assign row_number() to each record along with table code.
In second step we will remove all the duplicate records in the result ...
2
Unfortunately, the RETURNING clause of an INSERT can only work with columns from the inserted row. Columns added by a FROM clause are invisible there.
See:
RETURNING causes error: missing FROM-clause entry for table
To work around this limitation, I suggest to SELECT before each INSERT, and generate prospective new serial IDs in advance with nextval(). ...
Top 50 recent answers are included
